SQL Compact Edition - Intermittent Error on Insert

[RichS]

We are using SQL CE on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get an error intermittently as follows:

A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle

But the only PK on this table is RegTitleID.
The table structure is:

[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,

The problem seems to occur when a Title Number is inserted when a record with that number already exists. There is no unique constraint on Title Number.

Has anyone else experienced this and did you find a better solution than putting the .exe in C:\Windows?

 

[woogoo]

This may well be correct but there is obviously a unique index with this constraint.
--

woogoo

 

[foxdev]

I'm wondering if it is something in Synchronization Services that is messing up, such as looking up the RegTitleNumber, capturing its RegTitleID if found, then trying to insert it while specifying the RegTitleID (which it could only do if it sets its IDENTITY INSERT on).

Unfortunately, I know nothing of configuring Sync Services.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[RichS]

This appears to be fixed now. If anyone is interested in how:

We removed the problematic tables from the local data cache, deleted the .sdf (and its references) and reran the configuration wizard to add the tables back. Over the past several weeks, we've modified our process for schema changes. These tables were added early on. It seems that our current process conflicts with the results of our earlier methods.