[VS2005, SQL Mobile 3.0] A duplicate value cannot be inserted into a unique index.

[GottaGetITDone]

Hey Guys,

"A duplicate value cannot be inserted into a unique index."

I am getting this error while trying to INSERT records into a table that was 'pulled' using SqlCeRemoteDataAccess.Pull with RdaTrackOption.TrackingOnWithIndexes.

The table I am pulling looks like this:

CREATE TABLE [dbo].[tblPerson](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Firstname] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[LastName] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

I pull this table back to the mobile device and then attempt a simple INSERT statement like so:

INSERT INTO tblPerson([Firstname],[Lastname]) values('x','y');

Executing this statement on the mobile device returns the error as described above.

I believe what is happening here is that SQL Server Mobile's next idenitity seed is not being updated after the pull, when I call INSERT the next ID number is found to exist and the error is returned.

I have been doing some quite extensive Google searching and found that another solved this problem by moving the identity seed up like so:

"Select max(ID) As MaxID from AAA"
MaxID += 1
"ALTER TABLE AAA ALTER COLUMN ID IDENTITY (" & MaxID & ", 1)"

Where 'AAA' is a table and 'ID' is the PK with IDENTITY(1,1)

After these statements the next record that is inserted will get a unique ID and everything is good. This seems like a workaround - a hack. Surely there is a more correct way of doing this? I have not had a chance to try this out but the theory looks like it will work.

I found a post in which the same question was being asked and the reply was as follows:

> SQL Mobile 3.0 should not have this problem.
>
> Thanks,
> --
> Laxmi NRO, SQL Mobile, Microsoft Design Engineer

Well it is does have this problem Mr Microsoft Design Engineer - See the full post here

So people, what gives? Is there a way to code my pull method better to avoid this. Is it a SQL Server configuration issue? Is there a general fix? Should I be looking for a Service Pack of some description?

I would appriciate ANY thoughts, ideas, suggestions, previous experiences with this problem - ANYTHING. Even a better workaround if there is not a fix.

Thanks
Matt.

Using: Visual Studio 2005 - .Net CF 2.0, SQL Server 2005 Express SP1, SQL Server Mobile 3.0 and IIS5.1