Results 1 to 6 of 6

Thread: Urgent: How to drop identity nature of a column using T-sql(Sql server 7).

  1. #1
    Gopal Guest

    Urgent: How to drop identity nature of a column using T-sql(Sql server 7).

    I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn't able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

  2. #2
    Yariv Guest

    Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)

    execute the following prior to updating the table with the identity column:

    set identity_insert <table name> OFF

    Make sure you turn it ON after you&#39;re done.


    ------------
    Gopal at 12/19/00 12:41:11 PM

    I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

  3. #3
    gopal Guest

    Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)

    Hi Yariv,
    This statment &#34;set identity_insert <table name> OFF&#34; helps to insert a new records into a table in the place of already deleted records. For example, four records in table say 1,2,3 and 4. suppose 3 is deleted. then we can insert a new record in this gap(ie. with same value 3) using this &#34;set identity_insert <table name> OFF&#34;.

    ------------
    Yariv at 12/19/00 12:49:51 PM

    execute the following prior to updating the table with the identity column:

    set identity_insert <table name> OFF

    Make sure you turn it ON after you&#39;re done.


    ------------
    Gopal at 12/19/00 12:41:11 PM

    I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

  4. #4
    Ray Rankins Guest

    Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)

    Set identity_insert won&#39;t work. Neither can you use alter table to turn off the identity property. The only way I know of to turn off the identity property is to modify the system tables. Try this:

    sp_configure &#39;allow update&#39;, 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it&#39;s an identity column */
    where id = object_id(&#39;yourtable&#39
    and name = &#39;youridentitycolumn&#39;
    go
    exec sp_configure &#39;allow update&#39;, 0
    go
    reconfigure with override
    go


    Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:


    sp_configure &#39;allow update&#39;, 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it&#39;s an identity column */
    where id = object_id(&#39;yourtable&#39
    and name = &#39;youridentitycolumn&#39;
    go
    exec sp_configure &#39;allow update&#39;, 0
    go
    reconfigure with override
    go


    -Ray Rankins


    ------------
    Gopal at 12/19/00 12:41:11 PM

    I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

  5. #5
    gopal Guest

    Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)

    Hi Ray Rankins
    I finished that update work by updating system table &#34;syscolumns&#34; and i thought of entering solution for this. I received ur solution for this issue with clean explanation. This will help others to update identity column using these steps. But i&#39;m on the way to find out how to bring back identity nature of that field with custom seeds and increment(We can bring back identity nature with default seed=1 and incr=1 by setting colstat=1). If you know about this then let us know. Thanks for your reply.

    Regards
    Gopal
    ------------
    Ray Rankins at 12/19/00 4:10:56 PM

    Set identity_insert won&#39;t work. Neither can you use alter table to turn off the identity property. The only way I know of to turn off the identity property is to modify the system tables. Try this:

    sp_configure &#39;allow update&#39;, 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it&#39;s an identity column */
    where id = object_id(&#39;yourtable&#39
    and name = &#39;youridentitycolumn&#39;
    go
    exec sp_configure &#39;allow update&#39;, 0
    go
    reconfigure with override
    go


    Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:


    sp_configure &#39;allow update&#39;, 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it&#39;s an identity column */
    where id = object_id(&#39;yourtable&#39
    and name = &#39;youridentitycolumn&#39;
    go
    exec sp_configure &#39;allow update&#39;, 0
    go
    reconfigure with override
    go


    -Ray Rankins


    ------------
    Gopal at 12/19/00 12:41:11 PM

    I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

  6. #6
    Ray Rankins Guest

    Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)

    From what I&#39;ve seen, it does retain whatever seed and increment it had before setting colstat to 0 and back to 1. If you want to CHANGE the seed and increment after performing the update, you can change the seed by inserting a new row after re-enabling the identity property on the column using the &#34;set identity_insert&#34; command and inserting a new row with a value higher than the max identity value in the table. As for changing the increment, it looks like the original seed and increment may be stored in a binary field in syscolumns called autoval. How exactly the data is stored in there, I&#39;m not exactly sure. I&#39;d have to play around with it some on a test system to figure it out and how to update it.

    -Ray


    ------------
    gopal at 12/19/00 4:25:18 PM

    Hi Ray Rankins
    I finished that update work by updating system table &#34;syscolumns&#34; and i thought of entering solution for this. I received ur solution for this issue with clean explanation. This will help others to update identity column using these steps. But i&#39;m on the way to find out how to bring back identity nature of that field with custom seeds and increment(We can bring back identity nature with default seed=1 and incr=1 by setting colstat=1). If you know about this then let us know. Thanks for your reply.

    Regards
    Gopal
    ------------
    Ray Rankins at 12/19/00 4:10:56 PM

    Set identity_insert won&#39;t work. Neither can you use alter table to turn off the identity property. The only way I know of to turn off the identity property is to modify the system tables. Try this:

    sp_configure &#39;allow update&#39;, 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it&#39;s an identity column */
    where id = object_id(&#39;yourtable&#39
    and name = &#39;youridentitycolumn&#39;
    go
    exec sp_configure &#39;allow update&#39;, 0
    go
    reconfigure with override
    go


    Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:


    sp_configure &#39;allow update&#39;, 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it&#39;s an identity column */
    where id = object_id(&#39;yourtable&#39
    and name = &#39;youridentitycolumn&#39;
    go
    exec sp_configure &#39;allow update&#39;, 0
    go
    reconfigure with override
    go


    -Ray Rankins


    ------------
    Gopal at 12/19/00 12:41:11 PM

    I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •