none
Synchronize self-referenced table or hierarchical data using Sync Framework v2.1 and SqlSyncProvider RRS feed

  • Question

  • Hello,

    I’m using Sync Framework v2. and SqlSyncProvider’s to sync SQL 2008 R2 and SQL Express 2008.

     

    I have a table containing self-referenced FK, this means I may have as many hierarchy levels as I want. When I try to sync this table, I’m getting FK rule violations. It looks, that inserted rows for this table are being selected not it the order they were inserted but in some other way (because of PK index or something else… ??? PK column is ‘uniqueidentifier’ ) and Sync Framework tries to sync a row whose parent row has not been synchronized yet…

    I’m expecting analogous issues with deleted rows as well, i.e. even my application deletes children rows before parent I can’t be sure that Sync Framework will synchronize children deletions before parents (please, do not suggest to enable ‘delete cascade’ as it raises other sync issues, i.e. if in my client database I move all children to other parent and delete that ‘old’ parent row, Sync Framework syncs deletion operations foremost so disabled ‘cascade delete’ prevents from deleting child rows (in server database) that had been updated (became new parent) in the client db).

    Is this expected behavior of Sync Framework ?

     

    Thanks.

    • Edited by kambarda Tuesday, December 20, 2011 7:34 PM
    Tuesday, December 20, 2011 7:28 PM

All replies

  • After some experiments I hope I’ve found a workaround for this:

    after I changed tablename_selectchanges procedure for each table adding ‘order by local_update_peer_timestamp’ at the end I’m getting selected rows in correct order.

     

    Q1. Is this an acceptable/reasonable solution ? Or could you suggest something better ?

    Q2. Is there a way to force Sync Framework to add this ‘order by’ clause automatically during database provisioning ?

    Tuesday, December 20, 2011 8:24 PM
  • if i remember it right, Sync Fx applies changes in these order : Deletes, Inserts, Updates.

    and no, there is nothing in the the provisioining API for you to configure the order by

    Wednesday, December 21, 2011 1:53 AM
    Moderator
  • Sync Order (Deletes, Inserts, Updates) is in my mind.

     

    The problem I have described already appears synchronizing only Inserts for self-referenced table. Let’s ignore other operations. Sync Framework inserts child row before parent and it fails of course. For some rows this works ok, but for some it fails. I think it depends on the PK value ?! Without additional modifications in “tablename_selectchanges” sp's I can’t be sure that all rows will be selected in the order they were Inserted, i.e. parent rows before child.

    Wednesday, December 21, 2011 7:12 AM
  • another option will be to specify Retry on Next sync for the conflict/error and do multiple passes...
    Thursday, December 22, 2011 5:51 AM
    Moderator