Beware DEX_ROW_TS for data synchronisation

DEX_ROW_TS is a SQL server database field. It is found on sometables in Dynamics GP ERP system(since V10). The field contains a UTC timestamp that is “stamped” whenever that row in the database is changed, setting it to current time and date. A blog entry from David Musgrave, Understanding how Microsoft Dynamics GP works with Microsoft SQL Server, says that this was introduced to “help with Business Data Catalog (BDC) indexing for SharePoint searches”.

Run this script from Tim Foster, to see all the tables in the GP database that use it;

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%DEX_ROW_TS%'
ORDER BY schema_name, table_name;

Executing this script yields the following tables from using a test GP2010 database (including the DYNAMICS & Company database):

Figure: Tables containing DEX_ROW_TS

A database trigger is responsible for keeping the timestamp updated on changes. Below you can see the trigger that works against the table MC40200.

CREATE TRIGGER [dbo].[zDT_MC40200U] 
    ON [dbo].[MC40200] 
    AFTER UPDATE 
AS  
set nocount on 
BEGIN 
UPDATE dbo.MC40200 
SET DEX_ROW_TS = GETUTCDATE() 
FROM dbo.MC40200, 
inserted 
WHERE MC40200.CURNCYID = inserted.CURNCYID 
END set 
nocount off 

As can be seen from the script above, the timestamp is updated with the current server UTC time when a row insert occurs. There are similar triggers for updates to the rows. The time used is the time of the SQL server, set by using the TSQL function GETUTCDATE(). It is worth notingthat this date time is with no time zone offsets. It is easy to write SQL scripts that may break due to local time zones, remember to take this into account.

Using DEX_ROW_TS for data synchronisation

If the need arises to keep the Dynamics GP data replicated to a CRM system or say an ecommerce website in Azure, perhaps a data warehouse, then the field becomes very useful. The value of DEX_ROW_TS since the last sync can be compared checked across all the tables and allow the selection of only the rows that have changed and thus only those rows may be selected for sync or push to The Cloud.

Don’t get too excited, I’m about to put the fly in the ointment. Using the field DEX_ROW_TS is very useful, but there are issues with relying on it too much…

Only some tables have the DEX_ROW_TS column on them

It soon becomes apparent that the field DEX_ROW_TS is not ubiquitous over all tables (as was seen from the results of the earlier database query). Although it exists on many of the common tables used in integrations, it is also lacking from so many others. Unfortunately once it is not on one table that requires synchronisation, then a another solution needs to be sought for identifying changed rows.

Table IV00105, used in price list setup is an example of a table lacking the column.

Perhaps it is possible to infer the need to re-sync a record in some cases, from the change in a related record in a table that does contain the timestamp however treading down this route means introducing a lot of logic and database work for each update.

Tables can update the time stamp on different table, even if it didn’t change

YES, that is correct, the time stamp can lie! Tables such as Item Master are updated by any change in the Item Quantity table for the same item!

By scripting out the tables and looking at the triggers, it is found that six tables have cross table updates of this nature. A operation on a row in these tables will update its own time stamp, but also crucially it will update the timestamp of another table too, even if nothing has changed in that other table’s row!

All the occurrences where there are interdependencies between updates in one table and update of DEX_ROW_TS column updates in another table are illustrated below. All of these are in the company database, none are in the DYNAMICS database.

Be aware it is not just synchronisation where this can be an issue. Perhaps there are SQL jobs sending reports when rows have been changed, in the customer set up records. These could be wrong due to corruption from changed addresses in the address table.

SQL Server Change Tracking

If you use SQL server change tracking, then you may also experience the same misleading update. Obviously the cross table update would be logged as an update to both tables by SQL server change tracking. At least it is possible to query for the columns updated by using a column bitmask with change tracking.

Customer Created Custom SQL Triggers

A blog post by Milan of Merit Solutions highlights another danger that is introduced by the triggers of the DEX_ROW_TS , read it here Dynamics GP Workflow Tips for DEX_ROW_TS.

Essentially be careful that triggers may run twice as the triggers that keep the time stamp updated are operating on the same table that triggered the trigger (follow me?), a loop could be accidentally caused. The blog proposes the use of (NOT UPDATE (DEX_ROW_TS)) and sp_settriggerorder to prevent this happening and manage the trigger order, if required.