Interesting issues with the CRMDateTime continuum.

This is a guest post by a friend and colleague of mine: Leon Krancher. He has been digging into an issue regarding CRMDateTime and integration. Make sure you read this before you start your own integration!

Well I guess I’d better start off with a little introduction. My name is Leon Krancher and I’m a colleague of Ronald at Avanade. As we both work with CRM we occasionally find time to get together, have a beer and share experiences. Recently I told him about a problem I encountered while trying to create a service that would synchronize my customers Data warehouse with CRM. Ronald thought it was interesting and asked me to write about it for his blog. So here we go.

Let’s start out with a more detailed description of what had to be created.

The customer’s data warehouse contained information about its customers. This information is updated frequently by the customer’s backoffice systems as well as a number connections to third party systems. Whenever the data changed a database trigger would update a datatime field inside the database to indicate the exact moment the customers data was changed.
Because the data warehouse data was also used in CRM I was tasked with creating an application that would update the data stored in CRM whenever the data warehouse was updated. Unfortunately I only had a very limited timeslot in which this application would have to run so I had to find a way to update only those few records that had changed. To solve this problem I decided to do the following:

Ø First I would add an attribute to the entity in CRM that would be used to store the DateTime value used to store the date and time on which the data was originally created in the data warehouse.

Ø Second I wrote a cross-database join that would join the data warehouse on the CRM database, compare both datetime values and return those rows for which the entity in CRM was outdated.

Ø The last step was to use the CRM webservice to update the CRM entities returned by the previous query[1].

Unfortunately whenever I tried to run my tool it would always update all records even though the data warehouse had not changed.
After some frantic debugging[2] I finally figured out what the true problem was.

When storing my data in CRM I used the following code taken from the CRM SDK.

providedContact.new_nawsyncdate.Value = syncDateAccordingToDatabase.ToUniversalTime().ToString("u");

This converts the syncDateAccordingToDatabase DateTime object to a string in the universal time format so it can be stored in CRM. Unfortunately the universal time format omits any milliseconds.

So whenever SQL Server would compare the dateTime stored in the data warehouse to the dateTime stored in CRM the data warehouse DateTime that did contain any milliseconds would be seen as ‘larger’, hence the continuous cycle of updates.

After figuring this out the problem was easily solved by adjusting the database query so it would always remove any milliseconds before making the comparison.

I hope this provides you guys with both a funny story and some help if you ever run into a similar problem.

-Leon

[1] I couldn’t update the CRM database directly because that would prevent the execution of a callout

[2] In my defense, the matters were complicated because the data warehouse and the CRM databases were set to use different time zones making the problem appear like a conversion problem