Disarming IDENTITY generation

Ok, suppose I have created a new table with an Identity column, but I want to load the table from an existing table and I can’t let SQL Ev reset the Identity values. In SQL Server I can turn off Identity generation using

SET IDENTITY_INSERT biblio.dbo.authors OFF

Is there an equivalent in SQL Ev?

My next question is (if this does not work), can I use ALTER TABLE to disarm the Identity value and re-arm it after the load?

Bill,

no, you cannot disable the generation of identity values as you insert records into a table with an identity column, but you can alter the seed and increment values on the column using ALTER TABLE ALTER COLUMN standard syntax. For example, if the rows you are putting into the empty table begin with identity values starting at 1000, just create the table with this identity columns seed set to 1000.

if you want to know the last value of an identity column after one or more inserts, select the @.@.IDENTITY function against the table.

if there is any chance this table will be in an RDA or merge replication synchronization relationship with a server, I would strongly suggest you use uniqueidentifiers instead of identity columns on your table. this will avoid a lot of admin and possible conflicts if you are in a multi-user environment.

as I follow your posts, it sure sounds to me like you are in the process of creating a starter SQL Everywhere database based on a server-side database. Have you considered just setting up a quick merge replication and using the option to have the SQL Everywhere database created dynamically from the server upon initial replication (AddSubscription(AddOption.CreateDatabase))? I have posted a lot of info on this and done a few MSDN webcasts on the topic, so search this forum for 'starter database' for more details. this would solve your issues with identity column values and also with FK creation.

Darren

|||

Actually, I'm trying to write an EBook to supplement my new Hitchhiker's Guide to Visual Studio and SQL Server book. So far, all I've found is a million issues...

The current problem is that the ID values in the rows on the source table are joined logically to other tables. That is, the Author_ID is in the Title_Author table so it can't be arbitrarily changed without changing the other table(s) that are linked to the Author_ID. The same goes for the other tables. This makes it tough to load Identity values at all into a SQLCE database--I guess I'll have to give up on that approach. However, I don't see that using uniqueidentifiers will help unless I can go back and rebuild the PK/FK relationships on the source tables using them (a PIA).

Yes, I can see that I need to go back two squares and try to build a replication scenario.

My inital tests with SSIS did not work so that's a non-starter. I found that the host database uses datatypes not supported by SQL Ev including VarChar and Timestamp which chokes SSIS.

I expect I'll have to create a custom server-side database that's specifically designed to deal with all of these issues.

I'll check out your content. I would like to check out the SQL CF MVP pages (I'm also an MVP) can you drop a private note pointing to where you hang out?

Bill

|||

sure Bill - feel free to shoot me an email - I'm googlable and in the MVP directory as well.

-ds

|||

My two cents... With SQL CE 2.0 you could actually overwite the identity values through the OLE DB provider (a bit like in Access but this engine goes a bit further and even updates the seed). Microsoft dropped this behavior with SQL Mobile and this posed a big challenge for my database copy / sync tools. What now? Turns out, the only solution is to ALTER TABLE and set the seed to the next value to be inserted for every row. You will also have to reset the identity properties to what they are supposed to be at the end of the export procedure. To make all of this work, you also have to make sure you close the base table cursor (if any) before altering the identity value. This means more work for a SqlCeResultSet, but not for an INSERT command.

It's a lot of development work but the code is not slower than in the SQL CE 2.0 case, so it's quite workable and it is the solution I actually adopted.

|||

Interesting solution. I guess it would only be more convoluted if it involved live chickens.

I'm not sure I'll recommend this in my book other than mention it as an alternative...

Thanks

|||

I don't think that was going to work because the SQL Server 2005 Mobile Edition does not support Alter table as a SQL Statement .At leat I could not make it work.Right now I'm stucked on that problem and I would like to read other opinions

Nelson

|||

I would like to know how can I make it work using the alter table statement since I tried and it did not work.I tried using the SQL Server Editor in Visual Studio and at runtime .None of those options seems to work.

Thanks in advance