SQL CE and Sync framework – A duplicate value cannot be inserted into a unique index.

I am currently working on a project utilizing Sync Framework and SQL CE and ran into an annoying bug with identity fields that I wanted to make other developers aware of.

If you use sync framework to sync to a SQL CE database and you use identity fields then after the sync has occurred identity fields seed values will be reset to 1 after the sync has occurred.

This means should you then try and insert data into one of these tables you will receive the error “A duplicate value cannot be inserted into a unique index” as SQL CE believes the next value in the identity field is 1.

You can verify this is happening by examining the information_schema view before and after the sync:

select * from information_schema.columns where table_name = <tablename> and column_name = <columnName>

The solution?

Well I spoke to Sync framework team who said they have no plan to fix this issue (unbelievable!) so you have 2 main options:

Do an insert (this will fail) however SQL CE then seems to sort out the correct identity numbering and you are good to go

or

Alter the table and reset the seed value – note SQL CE doesn’t support DBCC CHECK IDENT command:

Foreach table in scope {

@currentId=max(RecordID) from table (Select max(RecordId) As MaxID from [test] )
Reseed current table to @currentId (ALTER TABLE [test] ALTER COLUMN RecordId IDENTITY ([@currentId], 1)
}

Ideally when you are performing synchronization you dont want to be using identity fields (bad things would happen with more than one client!) but probably GUID’s. In our project only one database can be master and we are prevented from making schema changes.

Advertisements

5 thoughts on “SQL CE and Sync framework – A duplicate value cannot be inserted into a unique index.

    1. Hi Andrew,
      I moved onto a different project so not sure what the eventual resolution was.It wasnt something the sync framework team looked like they were going to fix so I would imagine a manual fix was implemented.

      Alex

  1. Hi Alex,

    I am also facing The same problem, I got the main reason of this problem but i don’t know how to fix it. Let me tell u my scenario

    I am working with application which is Sync data between Client (*.sdf) and centralize server database (SQL) using WCF. And i have Identity field in table say tblEmployee Table.

    Whenever i am inserting data in client side and Sync that data then again inserting new record into client side database so its working fine, But when i am inserting data in server database table and Sync it then it will successfully Sync and new record also showing me in client database table.

    Now when i am going to insert new record in client database table then it showing error ” A duplicate value cannot be inserted into a unique index. [ Table name = tblEmployee,Constraint name = PK_tblEmployee] ”

    I think that the mechanism which is creating new id for Identity column at client side it is not automatically getting updated when i insert record on server and Sync it.

    I hope you understand what i am trying to say.

    Please Reply back to me if you have any solution.

    Thank You
    — Nitin

    1. Hi Nitin,
      Unfortunately I don’t think using an identity column is going to ever be fully supported and you will have to change to a unique identifier. As you say the identity seed value doesn’t seem to get updated.

      Its a long time since I looked at this but believe its possible to manually update the identity seed values with something like the DBCC CheckIdent command see http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/ which might then enable you to get the sync working.

      Good luck 🙂

Comments are closed.