locked
Update negative value PK from Local Database Cache to Positive value PK RRS feed

  • Question

  • My VB 2008 project is based on the occasional connected database paradigm using a local database cache (SQL Server CE) using bi-directional sync.  In it I have a table which has a master-child relationship with another table.  The CE database has its Primary Key (int seed -1) has an autoincrement of -1 and correctly saves the data to the parent and child with incrementing negative keys.  When I sync to SQL Server the data is save into SQL Server as negative values as well. 

    Do I have to write triggers in SQL Server to update the PK from the negative value and let the cascading updates update the children which will then be downloaded back to the client CE?  OR did I miss some step in which this is automated in some way?
    • Moved by Max Wang_1983 Wednesday, April 20, 2011 11:26 PM Forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Sunday, October 18, 2009 5:32 PM

Answers

  • Hi -
    No - this step is not autmated in any way so you are going to have to do this,
    Clearly you need to assign a new PK on the server that is then downloaded to the client CE. The best way to do this would be to assign this PK in the ChangesApplied event. If you still want to use triggers, you are correct about the trigger - but make sure that the updation of the PK does not happen in the same transaction as the reualr changes being applied which will result in no new changes being detected at the end of the sync.

    Thanks
    Deepa
    Deepa ( Microsoft Sync Framework)
    Wednesday, October 21, 2009 10:59 PM
    Answerer

All replies

  • Hi -
    No - this step is not autmated in any way so you are going to have to do this,
    Clearly you need to assign a new PK on the server that is then downloaded to the client CE. The best way to do this would be to assign this PK in the ChangesApplied event. If you still want to use triggers, you are correct about the trigger - but make sure that the updation of the PK does not happen in the same transaction as the reualr changes being applied which will result in no new changes being detected at the end of the sync.

    Thanks
    Deepa
    Deepa ( Microsoft Sync Framework)
    Wednesday, October 21, 2009 10:59 PM
    Answerer
  • Thanks. I imagine this is a very common problem and would be a good thing to see solved in the out of the box plumbing of the sync framework in the future.

    My app is for a small number of users.  To minimize the complexity and reduce network traffic I decided to manually seed each client with a different initial identity seed. Using a 4 byte int  and setting each user 1 million numbers apart meets my needs and will allow me to have 9000 users (I only plan on having a handful).  This will ensure no collisions and does not have the drawbacks of the using a GUID.  The artist in me hates it but I think it will do the trick.

    Wednesday, October 21, 2009 11:35 PM