[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

[Strider]

just as a point to note since you are using RDA you should really have a uniqueidentifier column in your table defaulting to a newid().
other wise if you are using a int Identity column and there is more the one device errors will occur when performing a push back to the server as there will be conflicting ID values

[GottaGetITDone]

Hey Strider,

Thanks for the input! Being my first mobile app I really appreciate any help I can get here.

I'm a little confused by your reply. Please forgive my ignorance but I believe I have a unique identifer. Column [ID] has a primary key constraint with an identity specification. I was under the impression this was considered a unique identifier.

Can you please explain your uniqueidentifier column a little more - even just a lead so I can research this on Google. You may have hit the nail on the head. If there is another type of unique id that can be used maybe this is the one I should be using? This could be the source of all my problems.

For information sake: In the table designer there is a property called 'Not for replication'. I currently have this set to FALSE for the [ID] column, should this be TRUE by any chance?

And yes there will be more than 1 device so uniqueidentifer may be the only way to go?

Thanks for your help Strider,
Matt.

[GottaGetITDone]

WOAAHH!!

I have ALOT to learn!

Just typed "sql server uniqueidentifier column" into Google and got a HUGE amount of results in relation to SQL Server CE! Looks like I have some reading to do!

I have already learnt the following from SQLTeam.com:
* Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)
* That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time.
* To get a GUID in SQL Server (7.0+), you call the NEWID() function.
* The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.
* This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.

Strider may I please ask a few questions:

Q. Would I totally scrap the IDENTITY column in favor of the UniqueIdentifier?
Matt's A. Probably Yes????
Q. How is this setup in SQL Server?
Matt's A. Table design, use the 'uniqueidentifier' type, set as primary key.
Q. What steps do I have to do in order to create rows? Is NewID() involved? Will it be auto generated when I INSERT or do I create it myself.
Matt's A. Both!: 1) Set the Default to call NewID() and it will be created for you or 2) Generate in code and pass into SQL Server. Prefer self generation because no '@@IDENTITY' feature for the last GUID created.

EDIT: Do you agree with my answers/assumptions?

I realise I am asking some pretty hefty questions and understand if the answers are too big. I am used I desktop database apps and know how to work with IDENTITIES, things like "Select @@IDENTITY", etc, etc. Any pointers, links, references, summaries would again be very much appreciated.

Im off to do some reading on MSDN!

Matt.

[GottaGetITDone]

Hey All,

OK I'm learning! I plan to post my findings here for others in a similar position, also I can post my assumptions, conclusions and thoughts so they can be verified, torn apart and left as record for other noobs.

First some reference links:

• Uniqueidentifier vs. IDENTITY
• HOWTO: Insert New Values to a Uniqueidentifier Column in SQL Server CE
• Using uniqueidentifier Data
• Need a GUID? Let VB.NET generate one for you!

As I learn and find more I will continue to edit this post...

Matt.

[Strider]

Q1. Yes you can get rid of the ID identity column and set the GUID as the primary key

Q2. Select unigueidentifier as the data type of the field and then in the properties set Is Row GUID to Yes, which will give the default value (newid())

Q3. The newid() above will create be created on any rows you add


these article should help you along with RDA
http://www.sqljunkies.com/tutorial/1...cd598cf46.scuk
http://www.devbuzz.com/content/eyecandy_01_pg2.asp
http://www.ondotnet.com/pub/a/dotnet...20.html?page=1

[GottaGetITDone]

Hey Strider,

Thanx for the links! I had previously seen the 'ondotnet' link - its a great article. Just about all the information required to get started in RDA is contained in those 3 links.

I am currently at my day job and have not yet had a chance to try the uniqueidentifier solution you pointed me to. I can't wait to give it a go though because the theory sounds perfect!

Once I get the solution implemented I will let you know how it went.

Again I REALLY appreciate your help here Strider!
Thanks mate,
Matt.