vnj


Hi,

I have Product table in SQL Server 2005 with Primary key ProductID. This is column with Identity set so the values for this column is auto-generated. I also have Orders table has a key that is foreign key to table Products. Now my dev environment has SQL ME whereas the deployment will be done on SQL CE.

Now I have a requirement where we want to synchronize the master tables from SQL Server with the device. For this we want to have the same identity values on the device. When I try to issue SET IDENTITY_INSERT Products ON on SQL ME it does not work. Is there any way I can set the identity ON on the SQL Mobile Edition

Also another question I have is: Is SET IDENTITY_INSERT supported on SQL Compact Edition

Regards,

vnj




Re: SET IDENTITY_INSERT for SQL CE and SQL ME

João Paulo Figueira


The SET IDENTITY_INSERT command is only supported on SQL Compact Edition 3.5. For 3.0 you can use an ALTER TABLE command to change the current IDENTITY value to the one you want, insert the data and then use that same command again to revert the IDENTITY back to the previous setting. To check the current value of the IDENTITY column use the technique I describe in this article:

QA: How do I get IDENTITY information on a SQL Mobile database







Re: SET IDENTITY_INSERT for SQL CE and SQL ME

asowles

I am using a different technique to get the value of the identity column. Immediately after an insert, I issue this:

If _inserting Then

cmd.CommandText = " SELECT @@IDENTITY;"

GeneratedID = cmd.ExecuteScalar()

If Sys.SystemID = -1 Then

Sys.SystemID = GeneratedID

End If

End If

Is there any reason that I should not do it this way I got this on the SQL Compact Blog.