locked
Sync conflicts after update primary key at source RRS feed

  • Question

  • Hi

    i have a system syncing multiple SQL Servers to a central one i get conflicts when the source system is doing update on primary keys in it table. from my understanding the conflicts arise from not having a delete for the old row (no trigger fired) and no insert of the new row (again no trigger fired) that causes the old version to be missing and new one raises the conflict in the server (update to a non existing row).

    And my question is:

    is there any way around it?

     

    Sunday, November 13, 2011 3:03 PM

All replies

  • what type of conflict are you getting?

    afaik, the update trigger used by Sync Fx wont actually be able to update the corresponding tracking table entry. if you look at the update trigger, its doing a join between the side table in the inserted logical table and its using the new PK value to join to the tracking table and the join condition will not be satisfied since the tracking table contains the older PK value.

    as a workaround, try modifying the update trigger such that if the PK was changed, you would do an Insert to the tracking table instead of an Update.

    If you look at the insert trigger, its doing something similar.

    Monday, November 14, 2011 6:05 AM
  • Changing the update to delete and insert was my first idea, its also better practice in my opinion. the problem is that the source system is legacy system which is almost impossible to change. and then there is the problem of updating all the FK's foe the tables.

    The conflicts i get are:

    * Cannot insert the value NULL into column 'datesql', table 'Table1'; column does not allow nulls. INSERT fails.

    * The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table2_Table3". The conflict occurred in database "DB", table "Table3".

    Monday, November 14, 2011 6:36 AM
  • can you share the schema and how you're updating it?

    for FK constraints, you might want to set the conflict resolution to RetryNextSync

    Monday, November 14, 2011 11:11 AM
  • If you meant the DB schema than im afraid i cant to share it. 

    as for conflict recovery i tried both force source side (one way sync) and RetryNextSync with no visible difference the conflict still remains.


    • Edited by kulight Tuesday, November 15, 2011 9:54 AM
    Tuesday, November 15, 2011 9:53 AM
  • cant do much then to help you if we can't reproduce the error.
    Tuesday, November 15, 2011 10:16 AM
  • Thank you any way i wish i could share it. my company will not allow me...

    if you want to reproduce you may try taking a synced db source and destination then run an update on the PK at the source side changing its value to one that did not exits before now run sync again. this is basically whats happening.

    Tuesday, November 15, 2011 11:17 AM
  • Tuesday, November 15, 2011 1:02 PM
  • Hi,

    I have stumbled upon the same problem. The source is the update trigger - it doesnt reflect the change of the PK, it leaves only the old record. So in the end, it tries to insert empty old record and new record will be completely lost.

    I am kind of disappointed because of this. I believe that autogenerated trigger should be smart enough to distinguish these both situations and do whatever is needed to save new record info. It would be great if MS provided templates for trigger generation, because i cant update trigger just one time, i would have to alter it from code on all the tables everytime my service starts. 

    I hope MS would do something about it in the future versions.

    Thursday, January 12, 2012 1:23 PM