This is a reposting of an article Sivakumar Venkataraman originally wrote on my Developing for Dynamics GP blog.
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!
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.