SQL Server 2008, SQLServerCE, Local Data Cache / Sync - Insert Er

[Adam]

Hi,

Hopefully someone can help me. I have a Visual Studio 2008 WinForms
application to which I have added a Local Data Cache. The local data cache is
using a SDF file via SqlServerCE and is caching a SQL Server 2008 dbase with
change tracking enabled.

This is working and I can do bidirectional syncs and the like.

The problem I am having is with inserting data in to the local tables that
have been created by the VS wizards. When I try insert data either
programmatically or even through the VS interface (show data) I get a error
stating “A duplicate value cannot be inserted into a unique index†due to a
primary key constraint.

The tables have a auto inc int primary key and it seems like the seed has
been reset back to one. This doesn’t happen to all the tables though, only a
few.

Any ideas how I can fix this?

Regards,
Adam

 

[Adam]

Hi,

Thanks for taking the time to answer my query.

I have tried altering the identity to -1, 1 which only provides two more
inserts before the constraint is hit again. That is id's -1 and 0.

The problem happens outside of the adapater. If I just run the local data
cache wizard and immediately view the data in the table and try and insert a
row (no code involved) I get a pk constraint error due to what I presume is
incorrect seed value. Same if I open the sdf in sql management studio and hit
it with sql directly.

I can't seem to use IDENT_CURRENT in SqlCE so i did a select @@identity
<tablename> and it returned 202, which is the number of rows in the table.
The max value for the identity field is 236 as a few rows have been deleted.
If I delete the row with the ID 202 and do an insert it works, which confirms
it has set the identity incorrectly.

if I execute a 'ALTER TABLE <tablename> ALTER COLUMN TableID IDENTITY (x,
1)' statement, where x is the Max(TableID+1) (237) after performing a sync,
the issue is resolved. This seems a bit clunky though and I wonder if this is
correct behaviour as propegating the correct seed seems like an integral
function?

i think I am missing something?

Thanks again.

Regards,

Adam