none
sql ce autonumber not working right RRS feed

  • Question

  • hi all,

    I have been struggling with this for a while now.  I have been around the block trying to get a working disconnected winform app to sync with my server.  

    here's my problem:

    I have a customers table with an autonumber CustomerID field (int).   In sql server 2008R2, if I already have customers with numbers 1 to 10, and those get pushed to my local data cache, and things are fine at that point. 

    The dataset in my app and the sql ce database has the identity field as (seed 1,step 1).  when the client creates a new customer, the new customer gets assigned a "1", and the local save wont happen because of a duplicate index.  isn't sql ce smart enough to know to move it on up to "11"?

    Then, even if it did set the next customer record to 11 on say, 5 clients, wont they all try to sync up to the server a customer with an id of "11"?


    ~george

    • Changed type Ge0rg3 Thursday, August 1, 2013 2:44 PM
    • Changed type Ge0rg3 Monday, August 5, 2013 2:48 PM
    Wednesday, July 31, 2013 4:45 PM

All replies

  • yup, this will cause an issue, you should change your auto ID to a unique ID (GUID). 
    Wednesday, July 31, 2013 5:48 PM
  • thanks, ....I was worried this might be the case.  I read somewhere this could be problematic if I enable failover clustering or transaction log shipping.  not sure why though. 

    so I guess I will have to kiss the human-readable number goodbye then?  kind of sucks, because I don't want to have to refer to a customer order record by {9947BEDA-3823-BCCA-8389BEFF-93983FAA}.   would be nice to just have "Order 1023".  also, when using guids for row id's, do you typically set these in code or let the database assign it?


    ~george


    • Edited by Ge0rg3 Thursday, August 1, 2013 3:27 PM
    Wednesday, July 31, 2013 5:58 PM
  • I do it in code, I like to have full control over whats being created.

    Thursday, August 1, 2013 12:13 AM
  • Sql Ce or not, Sync Framework or not, using identity columns as PK for syncronization will have issues.

    Not unless you use some kind of ID partitioning (similar to what SQL Replication does).

    e.g., client1 gets IDs 1-100000, Client2 gets IDs - 100001-200000, etc...

    Thursday, August 1, 2013 3:24 AM
    Moderator
  • racing prog...

    thanks for all your input! 

    That being the case, I thought pretty hard on this last night.  here's my conclusions:

    Sync framework works fine for editing existing records and doing conflict resolution on existing records AND Sync framework with multiple disconnected clients simply cant handle the generation of keys in this type of environment without the use of row guids.

    Therefore, I thought I could use a local database for each client, and when a new record is created locally, I will give these records a primary key value of "-x" (stepping -1 for each new record).  this way, all client databases with new records have negative numbers. 

    When a sync is requested, I will manually do inserts to the server, immediately returning the identity scope value to do inserts on child tables.  once those particular entries has been inserted to the server database,  then locally do a "delete from ___ where id < 0".....no more local record with negative index.  

    Next, launch the sync provider to do normal sync changes (with conflict resolution) and download all records from the server, which will include those newly inserted records with server-assigned keys.

    thoughts?  suggestions?


    ~george

    Thursday, August 1, 2013 2:45 PM
  • Sql Ce or not, Sync Framework or not, using identity columns as PK for syncronization will have issues.

    Not unless you use some kind of ID partitioning (similar to what SQL Replication does).

    e.g., client1 gets IDs 1-100000, Client2 gets IDs - 100001-200000, etc...


    noted. thanks june!  I might have figured out another way. 

    ~george

    Thursday, August 1, 2013 6:47 PM