SQL Mobile - identity column

  • Thread starter Thread starter Chris Botha
  • Start date Start date
C

Chris Botha

Don't know how I'm going to get around this. The idea is that a dataset is
selected from the server using Web Services. Some tables has Identity
columns and I would like to keep them Identity columns in the SQL Mobile and
have the Identity values coming from the server preserved in the table.
Inserting a value into an Identity column is obviously not allowed.
So my solution was to not have the column as an Identity initially, then
insert the data from the server and so the values are preserved. Then to do
an "ALTER TABLE" and set the column to be an Identity column. It does not
work, the error is "The column cannot become an identity column after it is
created".
Strange, they have an example in the SQL Mobile books online with this in
the "ALTER TABLE" section.
In SQL Server there is the "SET IDENTITY INSERT " statement allowing this,
but is not supported by SQL Mobile.

Any ideas or pointers are welcome too.

Thanks.
 
You need to do an identity translation in the web service (ugly - you have
to track the device and filed ID), a key that's not an identity (like a
GUID) or use a PK check-out scheme.

-Chris
 
Hello Chris,

I solved this problem in my products by tracking the IDENTITY value and changing
it through an ALTER TABLE when necessary. This way you can get an exact copy
of the IDENTITY columns in the target database, but at the expense of some
effort.

Regards,
João Paulo Figueira
eMVP
 
What happens when you have multiple devices in the field adding to the same
table?

-Chris
 
Hello ctacke/" ctacke_AT_OpenNETCF_com,

I also solved this in the forthcoming Data Port Sync by partitioning the
IDENTITY space: The desktop database uses the regular IDENTITY values where
the PDAs use negative seeds and increments. For a 10 PDA scenario, you declare
all identities like this:

PDA 1: IDENTITY(-1, -10)
PDA 2: IDENTITY(-2, -10)
PDA 3: IDENTITY(-3, -10)
....

Of course, you have a 10 PDA limitation, but then again you can set the increment
to -100 or another arbitrarily large negative value.

Regards,
João Paulo Figueira
eMVP
 
Hi João, this sounds interesting but I am not sure what you did. The ALTER
TABLE was what I had in mind. So what I tried is creating the table and
don't specify that the column is an IDENTITY column at first. Now import the
data from the incoming dataset and the IDENTITY values are fine. Now
attempting to use ALTER TABLE to change the column to an IDENTITY column and
then I get the error mentioned.

Do you mind elaborating a bit on what you are doing?

Thanks.
 
Hi Chris, I resolve this on the server side. I have an extra column in the
table on the client side indicating if it is a new record. The server
knows/examines the schema of the database and first insert the primary key
table records, record by record, for each getting back the new identity and
then updating the relevant foreign key values in the DataTables of the
incoming DataSet before inserting/updating those records.
 
And then you push all changed records back to the client (so the recods it
inserted all get replaced or updated)? Interesting.

-Chris
 
Hi João, thanks again. Unfortunately this will not work for me, I think, as
I am calling a Web Service to obtain the data and then the SQL Mobile
database is built on the fly.

Thanks though.
 
Back
Top