SmartConnect 2018

MSSQL Table Real-Time Data Source

A MSSQL real-time data source will create a trigger against a MSSQL table that will call the SmartConnect WCF REST service.  This requires SQL Server version 2012 or higher.


To create an MSSQL real time data source:

1.Select Real Time Data Sources from the Map tab.
2.Select MSSQL Table Real-Time Data Source from the drop down list.
3.Select Create to add a new data source.
4.Either select the pre-defined connection, or enter the connection details as required.
5.Select the database / table as required.
6.If only changes to specific fields should trigger the real time data source, select the Restrict Columns button and select the columns that should trigger the data source.
7.To amend the query that should be used when the data source is triggered select the Define button and enter the required query into Query Builder.
8.Select Save to record the data source.


Real Time Data Source MSSQL


Real Time Data Source Fields


To add a MSSQL table real time data source to a map:

1.Select create or select the Maps Quick Link and double click on the map to be edited.
2.If a new map enter the map id and description.
3.Select the Data Source button on the map toolbar.
4.Select Real Time Event as the data source type.
5.Select MSSQL Real-Time Data Source as the Data Source.
6.Use the lookup to select the required real time data source. Note: the real time data must already have been created using the steps above.
7.To preview the columns that would be returned by this data source select the Preview button.
8.To validate the query select the Validate button.


Real Time MSSQL Data Source in Map



To register a MSSQL Table real time data source:

1.Open the SmartConnect map setup window. Using the Microsoft Dynamics GP interface go to Tools >> SmartConnect >> Map. For the external SmartConnect interface go to Map >> Setup, or select the Maps Quick Link and double click on the map to be edited.
2.If not already selected select the map to be registered.
3.Select the Register button on the map toolbar to open the Real Time Map List window. All maps linked the real time data source used in this map will be displayed.
4.If this is the first MSSQL Table real time data source to be registered, you will receive the following message.  You will need to copy the script (starting with IF EXISTS) in the message detail and run it in MSSQL.


5.After the script is run, you will need to select the Register button the map toolbar again.
6.Select the map(s) and event(s) that should trigger this real time data source.
7.If more than one map is linked to this real time data source, and the order the maps are required to run is important, select the Map Sequence button on the toolbar. This will open the Real Time Registration window and allow the order of maps to be specified. Specify the required map order and select OK to close the window. Note: maps must be selected in the real time map list window before they will appear in the real time registration window.
8.Select Process to update your changes. This will save your changes in SmartConnect and make the required changes to GP. Note: If users are logged into GP they will need to log out and in again before these settings will take effect.

Real Time Data Source Registration GP