Sync SDK 2.1 - Timestamp in Primary Key not supported RRS feed

  • General discussion

  • I'm experiencing problems Provisioning tables that have the datatype: Timestamp in the primarykey

    ERROR  , SyncApp, 9, 03/14/2012 15:04:56:276, Caught exception: System.Data.SqlClient.SqlException (0x80131904): A table can only have one timestamp column. Because table 'DKBooking_tracking' already has one, the column 'local_update_peer_timestamp' cannot be added.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncTrackingTableHelper.CreateTable(SqlTransaction trans)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans)

    Obviously the problem is that the provisioning script, copies the primary key fields into the _TrackingTable using the correct datatypes - but since the _TrackingTable is defined having its own TimeStamp column, this operation is impossible (Not Allowed) in the SQL Server.

    Navision uses Timestamp as primary key in all tables - but uses it as a UINT64 instead of actually a timestamp !!!

    Question 1 : Is Timestamp supported in Sync SDK 4.0 as primary key in tables ?

    Question 2 : Would it be possible to simply convert Timestamp to UINT64 in the _trackingtable ? (either the foreign key or the default timestamp)

    Monday, March 26, 2012 9:12 AM

All replies

  • 1 - if by Sync SDK 4 you mean the Sync Framework Toolkit, it's still built on Sync Fx 2.1 and nothing has changed in the SqlSyncProvider. so the answer is there is nothing new as far as data types or PK restrictions.  so, the answer is no.

    2 - converting to bigint is actually your only choice as technically, you cannot insert an explicit value on a timestamp column. if you look at the tracking table, all the the other timestamp columns is a bigint.

    if you have other columns in your table that is unique, you can actually "trick" sync framework to use that column instead of the actual PK. see this link: http://jtabadero.wordpress.com/2011/07/20/part-3-synchronizing-tables-where-client-and-server-primary-keys-are-different/

    Monday, March 26, 2012 10:25 AM
  • Navision is a Microsoft ERP system much like Dyncamics AX and C5, Concorde XAL etc.
    it generates the tables automatically, including primary keys, indexes and so on - I can't change the datatype of this reason.

    More importantly - as you state in 2) i can't insert values into a timestamp column - meaning, i can't sync Navision tables : since they are declared with a timestamp column :(


    Monday, March 26, 2012 11:15 AM
  • i think you can get away without changing the ERP side. you just have to play around with the sync fx objects and do data conversion on the fly. for example, on the selectchanges sp, you can cast the timestamp to a bigint assuming you just want a one way sync. if you want to do a two way sync, then it becomes tricky.

    • Edited by JuneT Monday, March 26, 2012 11:28 AM
    Monday, March 26, 2012 11:27 AM
  • Luckely the table only has to do one-way sync... FROM the ERP TO a 'dead' table 

    using the DbSyncTableDescription and DbSyncColumnDescription i might be able to define the type of the Timestamp as BIGINT instead, thereby tricking the tracking table to store the data anyway.

    I will investigate if this is possible... 

    Monday, March 26, 2012 11:40 AM
  • Thinking more about this brutal table-design i've come to some conclusions:

    1. Row INSERTS and DELETES can be synced, but UPDATES will create duplicates due to the nature of Timestamp

    2. only 1-way sync is possible

    3. trackingtable needs to be tweeked using DbSyncColumnDescription to define an alternative datatype to timestamp (bigint) 

    4. DBA's who use timestamp as primary key should find another job. 

    Monday, March 26, 2012 12:12 PM
  • can you elaborate on item #1?

    no comment on #4 as i might just find myself working with one in the future :)

    Monday, March 26, 2012 12:27 PM
  • 1)

    Really depends on how the Triggers work, 

    1. When inserting a row, the Timestamp (TS) column gets the ID of 500
    2. When updating the row, the TS gets the ID of 501
    3. When deleting the row, the ID is now 501

    in case (2) i supect that the PK is not updated until AFTER the update took place, therefore the meta-data is refering to PK = 500. The Question is therefore wheather the trackingtable records this change of PK-ID or it doesnt. 

    It all depends on weather the Sync-triggers record the PK change, and that it's able to propagate that change to the remote server ?

    Monday, March 26, 2012 12:42 PM
  • unfortunately, sync framework dont handle pk value changes... the best way to handle it is to do a delete and insert...you can change the update trigger that updates the tracking table to do this.

    Monday, March 26, 2012 12:53 PM
  • Thanks !

    I'll keep that in mind - dont know if it will be necessary or we'll find another solution. !?

    Thanks for the answers !

    Monday, March 26, 2012 1:42 PM