Sync Issue with MS SQL CDB - Datetime field overflow

[former_SQLA_member1694889]

Hello everybody,

we are using MobiLink in version 17.0.0.1211 in combination with a MS SQL 2014 SE. When we try to synchronize with our UltraLite client, we get the following errors in the mobilink server log:

I. 2018-11-15 13:48:03. <2>  UPDATE ml_subscription SET user_id = ?, progress = ?, last_upload_time = ? WHERE rid = ? AND subscription_id = ?

E. 2018-11-15 13:48:03. <2> [-10002] Consolidated database server or ODBC error:  ODBC: [Microsoft][ODBC Driver 13 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. (ODBC State = 22008, Native error code = 0, Column or parameter #3)

E. 2018-11-15 13:48:03. <2> [-10031] An error occurred when trying to store progress information in the consolidated database

W. 2018-11-15 11:22:54. <3> (,XXXXXX) [10017] The consolidated and remote databases have different timestamp precisions.  Consolidated database timestamps are precise to 0 digit(s) in the fractional second while the remote database timestamps are precise to 6 digit(s)

Any idea how to tackle this issue?

Thanks and best regards, Alex

[VolkerBarth]

What data type do you use on the consolidated? See that mapping for further reference. You can also adapt the precision SQL Anywhere uses for datetime values.

[former_SQLA_member1694889]

If I read the log file correctly, the error is in the ml_subscription table on the field last_update_time. This table is generated by mobilink and the field is of type DATETIME.

The last statement that is executed before the update is:

I. 2018-11-15 13:48:03. <2> SELECT {FN NOW()}

If I compare the field in the db with the output of above statement, I think that they have the same format:

SELECT {FN NOW()} --> 1900-01-01 00:00:00.000 ml_subscription.last_upload_time --> 2018-11-15 14:07:40.103

[Breck_Carter]

Do you see this message near the top the MobiLink log? (you probably should): Resolve the timestamp precision mismatch by setting the SQL Anywhere DEFAULT_TIMESTAMP_INCREMENT opt...

[VolkerBarth]

There is a bunch of hits on that MS SQL Server error message. Both values are differing in the number of fractions (0 ms vs. 103 ms). Basically I think you will have to restrict UltraLite to use timestamp values with a precision of 1 second.

[former_SQLA_member1694889]

Thank you for your suggestion, I followed it and set the value to 1000000, as suggested in the log file. However I am still facing the same issue.

Also tried it with TIMESTAMP_INCREMENT=60000000, but still same result. Any other idea?

[VolkerBarth]

So did you also follow that part of the description for warning code 10018:

UltraLite clients need to be regenerated after the precision is adjusted.

[former_SQLA_member1694889]

Hello everybody,

I think we all have been on the wrong track here - I changed the TIMESTAMP_INCREMENT on the udb, regenerated it and that did not help.

I suspected that it is a server internal problem (not connected to the remote dbs) and run SELECT {FN NOW()} followed by an update on ml_subscription.last_upload_time from the MS SQL Studio and that also worked fine.

At the end, it turned out that I used the wrong ODBC driver - version 13 and 17 lead to the error, when using version 6.x, it worked fine. Microsoft changed the way the driver works, newer ones do not throw warnings for overflows, but errors.

[VolkerBarth]

Well, it's documented that this is a MS SQL ODBC driver issue, just search for the according MS SQL error message - however according to those docs, an "appropriate" increment of 1 seconds (i.e. without fractions) should work with newer drivers, too. Hm.