Qlik Replicate: Azure SQL Database target datetime datatypes

[Alan_Wang]

Datetime2 datatypes should be used in place of the datetime datatype. Qlik Replicate tasks will default to datetime2 datatypes although the datetime datatype can still be used. The datetime datatype can have a loss of precision for milliseconds when in use through the ODBC layer for update operations. This may cause updates to not apply as the loss of precision for the timestamp will cause a mismatch with the original record. The Replicate task will proceed to move the update to the apply_exceptions_table and treat the updates as '0 rows affected'.

This scenario will affect tables that do not have Primary Keys or Unique indexes and MS-CDC is used for replication. In the event that MS-CDC is used, the before images of the datetime columns are also used to check for changes. The before image will not be found due to mismatches from loss of precision.

Resolution

In the table settings of the table, change datetime datatypes to datetime2 datatypes. The tables will need to be reloaded.

Cause 

The design of the datatime datatype goes through some calculations that lead to inaccurate millisecond values for updates when ODBC is involved. Millisecond values are only accurate if they end with 0,3,7 values. Other values are off and will cause a mismatch between the original record and the new copy of the before image.

Datetime2 is the redesigned datatype of datetime and does not exhibit the same behavior with the use of ODBC.

Internal Investigation ID(s) 

RECOB-7133 

Environment

Qlik Replicate 
Microsoft Azure SQL Database target endpoint