Post reply

SET IDENTITY_INSERT not working. Why?

  • August 11, 2004 at 7:54 am

    #360670

    Hi,

    I want to insert predefined id values in a column which is a IDENTITY

    column.

    My script is something like this --

    SET IDENTITY_INSERT tablename ON

    INSERT statement

    SET IDENTITY_INSERT tablename OFF

    But this works on one server and not on other server. Why?

    Do I need to change any server setting(s)?

    Please help.

    Thanks & Regards

    Niranjan

  • August 11, 2004 at 12:30 pm

    #518560

    I don't see anything wrong with your syntax and I'm not aware of any server settings that would affect it.  Are you getting any kind of message when running the script?

    Greg

    Greg

  • August 11, 2004 at 11:19 pm

    #518584

    Thanks for your reply Greg.

    Well the message it gives is a normal message shown when IDENTITY INSERTS are off.

    Ie "An explicit value for the identity column in table 'tablename' can only be specified when a column list is used and IDENTITY_INSERT is ON."

    When I tried to look at the server settings on both the server by using sp_configure, on the server where it works fine the allow updates value is 1 while on the server where it is not working it is 0. Could this be the problem? I am not sure about changing this setting.

    Thanks.

    Niranjan

  • August 12, 2004 at 2:12 am

    #518605

    Try using a column list in the insert-statement.

    SET IDENTITY_INSERT TableName ON

    INSERT INTO TableName (IDField, Field2, Field3, etc)

    VALUES (IDValue, Value2, Value3, etc)

    SET IDENTITY_INSERT Tablename OFF

    This should work.

    Regards,

    Anders Dæmroen
    epsilon.no

  • August 12, 2004 at 7:28 am

    #518660

    Great !!!

    it worked. I was not giving the column list in my insert statement and hence it was not working.

    thanks guys.

    Niranjan

     

Viewing 5 posts - 1 through 4 (of 4 total)

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