Utility Script for CRM Connector Integrating with Dynamics GP Sample Company

This is an interesting script which I have decided to post in this article.

When we configure CRM Connector with Dynamics GP for the sample company (Fabricam, Inc.,), there is a known issue in the way the sample company data is structured.

As many of you would be aware of, the sample company data has a date stamp in the year 2017. Because of this, when the Connector maps are activated, the sample data gets integrated and the “Check for data modified after” gets updated to the date as per the date stamp, which will be a date in the year 2017.

And when we add any new records into the sample company, this data does not get integrated as the DEX_ROW_TS field (date stamp) for the new records will be the current date and the Connector integration ignores these records as it checks for data only added/modified after the date in the year 2017 as per the original sample data integration.

You can modify the “Check for data modified after” property for the integrations to the current date and time, but it will check for the data in the year 2017 and get updated back to 2017. This is because the sample company’s data resides with the date stamp of 2017.

I have written a simple script which will update the DEX_ROW_TS in all Dynamics GP tables to the current date and time. This script can be run in both the system database and the company database.

SQL Script for this purpose:

DECLARE @TableName VARCHAR(15)
DECLARE @DateStamp DATETIME
DECLARE @SQLString VARCHAR(255)

SET @DateStamp = GETDATE()

DECLARE crTables CURSOR
    FOR
    select name from sys.objects where type = 'U' ORDER by name

OPEN crTables

FETCH NEXT FROM crTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
   
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns
        WHERE TABLE_NAME = @TableName
        AND COLUMN_NAME = 'DEX_ROW_TS')
    BEGIN
  
  SET @SQLString = 'UPDATE dbo.' + LTRIM(RTRIM(@TableName)) + ' SET DEX_ROW_TS = ''' + CONVERT(VARCHAR(10), @DateStamp, 120) + ''''
        EXECUTE(@SQLString)

    END
    FETCH NEXT FROM crTables INTO @TableName
END

CLOSE crTables

DEALLOCATE crTables

Note: Make sure you have a valid backup of the system and company databases before running this script.

I hope this script will be useful for all those who are trying to test CRM Connector for Dynamics GP using the sample company. I have attached this script at the end of the article.

Until next post!

Attachments:  MBS_CRMConnectorwithGP_DEXROWTSUpdate.zip