Alter column identity with Microsoft.SqlServer.Smo

Question

I've trying to change a column IDENTITY using Microsoft.SqlServer.Smo from a table with no dependencies and with all the data loaded previously (from another DB) but i get an error like this "Modifying the Identity property of the Column object is not allowed. You must drop and recreate the object with the desired property". The thing is that i tried to do this with Management Studio and it has no problem with it. Do you have any suggestions?. Thanks in Advance

This is the code:

foreach (Column source in sourcetable.Columns)
{
    try
    {
        if(source.Identity)
        {
            Column column = copiedtable.Columns[source.Name];

            // column.Computed = source.Computed;
            // column.ComputedText = source.ComputedText;

            column.Identity = source.Identity;
            column.IdentityIncrement = source.IdentityIncrement;
            column.IdentitySeed = source.IdentitySeed;

            column.Alter();
        }
    }
    catch { }
}

You cannot add or remove the `IDENTITY` from an existing column. Since this is not possible in T-SQL, it's also not possible using SMO ..... — marc_s, Oct 31 '14 at 05:57

score 2 · Accepted Answer · answered Oct 31 '14 at 04:39

Try doing it in SSMS again and choose to script the action out instead of applying it directly. You'll find that it creates a temporary table with the identity property set to true (and everything else the same), copies your data from the live table into the temp table, drops the live table, and renames the temp table to be the live table. You'll need to do something similar with SMO.

Copying the table is easy enough: iterate over the columns, indexes, foreign keys, etc and create your new table that way (taking care to set the identity property correctly properly before you call Create()). For moving the data, take a look at the Transfer class. Once that's done, it's a drop and rename (or a rename and rename if you want to be safe).

I'm a little surprised that SMO doesn't do this somehow under the covers (since SSMS uses SMO under the covers). If I find something else that makes it do this automatically, I'll let you know.

There is a [way to do it in SQL](http://stackoverflow.com/a/6086661/15498) without all of the copying shenanigans but I'm not sure if there's a decent way to duplicate that technique via SMO. — Damien_The_Unbeliever, Oct 31 '14 at 07:37
Thanks, in fact SSMS drop and copy a new table as you say. i had to make an script similar to SSMS for each table i tried to export — hexehell00, Nov 03 '14 at 18:42

Steve G · Answer 2 · 2014-10-31T15:14:16.333

0

Are you trying to update an existing record, or add a new record? If you're adding a new record, then do an insert. If you're updating an existing record, don't overwrite the identity column value.

To insert identity values into a table in SQL Server you must tell the database to allow you to do this. Syntax is:

Set Identity_Insert [table] ON

When you're done you need to turn it off again.

Set Identity_Insert [table] OFF

edited Oct 31 '14 at 15:14

answered Oct 31 '14 at 02:45

Steve G

993
1
7
14

Im not triyin to update or insert a new record. im triying to make an identity for preloaded column with the data from another Data base, but this new table doesnt have any dependencies and i dont know how the management studio can do that without droping any object – hexehell00 Oct 31 '14 at 02:54
If you're copying data from another database what you're doing in database terms is inserting data into the table. To do this you need to set Identity Insert on. See my edit to the answer above. – Steve G Oct 31 '14 at 15:10

Alter column identity with Microsoft.SqlServer.Smo

2 Answers2