Post reply

Problem with Identity Specification in SQL Server 2008

  • April 17, 2009 at 1:12 am

    #133059

    Hi all,

    I have one problem with Identity Specification in sql server 2008,

    i.e. Once I am having records in one table and after that if I want to change identity from Yes to No / No to Yes. Unable to do it 🙁

    Am i missing something or is it bug ??

    Any solution for that?

    Thanks in advance...

  • April 17, 2009 at 2:00 am

    #979463

    Please post the error message you got.

    There's no such bug in 2008

  • April 17, 2009 at 2:36 am

    #979470

    Hi,

    Thanks for replying...

    Here is the Error message that i got when i m changing the identity column to "Yes" and save the changes.

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    I can't understand what i have to do now??

  • April 17, 2009 at 2:58 am

    #979483

    krishrana17 (4/17/2009)

    Hi,

    Thanks for replying...

    Here is the Error message that i got when i m changing the identity column to "Yes" and save the changes.

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    I can't understand what i have to do now??

    use "SET IDENTITY_INSERT" command, for more info, see SQL BOL.

  • April 17, 2009 at 3:56 am

    #979515

    That's not a bug. It's a safety measure added to SQL 2008's management studio to indicate that the change that you've requested to the table requires that the table be dropped and recreated. It's there so that people don't 'accidentally' make changes in production that will take hours.

    You can switch that warning off, if you wish. It's somewhere under the options menu, I don't recall offhand where.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 17, 2009 at 3:58 am

    #979518

    Kishore.P (4/17/2009)

    krishrana17 (4/17/2009)

    Hi,

    Thanks for replying...

    Here is the Error message that i got when i m changing the identity column to "Yes" and save the changes.

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    I can't understand what i have to do now??

    use "SET IDENTITY_INSERT" command, for more info, see SQL BOL.

    I don't see the relevance?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 17, 2009 at 4:39 am

    #979531

    Hi GilaMonster,

    This is perfect answer...I got it 🙂

    Yes, its in the Option menu...

    Thank u very much...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply