Utility Script for CRM Connector Integrating with Dynamics GP Sample Company

Sivakumar Venkataraman - Click for blog homepageThis 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.)

MBS_CRMConnectorwithGP_DEXROWTSUpdate.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

One thought on “Utility Script for CRM Connector Integrating with Dynamics GP Sample Company

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.