Getting Error: Cannot update identity column

  • Dear Forum,

    First of all, I would like to thank you for your generous help in the past.

    I am getting the error: Cannot update identity column 'EventID'. EventID is my identity column. But I did not get this error until I moved my database and Storedprodedures to another SQL 2000 server.

    Matt Miller, another poster suggested that I put in the following commands:

    SET IDENTITY_INSERT shows ON

    your insert statement

    SET IDENTITY_INSERT shows OFF

    Where in my Stored Procedure should I put this? Any ideas why this happened after moving to another server?

    Thanks

    Jeff

    Boise, ID

    Here is my stored procedure:

    CREATE PROCEDURE Item_Update_Show

    (

    @EventID int,

    @Title varchar(50),

    @_Date datetime,

    @Venue int,

    @HeadlinerID int,

    @Opener varchar(150),

    @Doorstime varchar(50),

    @Showtime varchar(50),

    @Price varchar(50),

    @Onsaledate datetime,

    @VendorID int,

    @TicketURL varchar(250),

    @Description varchar(1000)

    )

    AS

    UPDATE Shows

    SET EventID = @EventID,

    Title = @Title,

    _Date = @_Date,

    Venue = @Venue,

    HeadlinerID = @HeadlinerID,

    Opener = @Opener,

    Doorstime = @Doorstime,

    Showtime = @Showtime,

    Price = @Price,

    Onsaledate = @Onsaledate,

    VendorID = @VendorID,

    TicketURL = @TicketURL,

    Description = @Description

    WHERE EventID = @EventID

    GO

  • Actually, if I read your stored procedure right, you don't need to do this. You're taking the EventID being passed in via the parameter @EventID and modifying the row corresponding to @EventID right? If so, you're not actually altering the EventID value, correct? Your update statement is just setting it back to the same value. If that's the case, don't change it in the UPDATE statement. Rewrite your stored procedure like so:

    CREATE PROCEDURE Item_Update_Show

    (

    @EventID int,

    @Title varchar(50),

    @_Date datetime,

    @Venue int,

    @HeadlinerID int,

    @Opener varchar(150),

    @Doorstime varchar(50),

    @Showtime varchar(50),

    @Price varchar(50),

    @Onsaledate datetime,

    @VendorID int,

    @TicketURL varchar(250),

    @Description varchar(1000)

    )

    AS

    UPDATE Shows

    SET Title = @Title,

    _Date = @_Date,

    Venue = @Venue,

    HeadlinerID = @HeadlinerID,

    Opener = @Opener,

    Doorstime = @Doorstime,

    Showtime = @Showtime,

    Price = @Price,

    Onsaledate = @Onsaledate,

    VendorID = @VendorID,

    TicketURL = @TicketURL,

    Description = @Description

    WHERE EventID = @EventID

    GO

    K. Brian Kelley
    @kbriankelley

  • Brian is correct...

    What I really want to know, is how that might have worked in 2k at all... would have given the same error with the code displayed by the OP.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you so much. That worked perfectly.

    That is a very good question about how did it ever work? The only difference on the systems, is maybe the old system only had sp3 installed.

    Thanks

    Jeff

  • Jeff Wood (11/18/2007)


    Thank you so much. That worked perfectly.

    That is a very good question about how did it ever work? The only difference on the systems, is maybe the old system only had sp3 installed.

    It should have failed on any version of SQL Server 2000. 🙂

    K. Brian Kelley
    @kbriankelley

  • Try following trick...

    update emp set empid = 100 where empid = 10

    The above statement gave me following error as empid is identity column

    Cannot update identity column

    Hence I did following and achieved the effect of update

    set identity_insert emp on

    insert into emp (empid,empname,designation) select 100,empname,designation from emp where empid = 10

    delete emp where empid = 10

    set identity_insert emp off

    * Important Note : If your identity column is also a primary key and is being referenced as foreign key with CASCADE UPDATE and CASCADE DELETE enabled in detail table, please first drop the constraint or disable the CASCADEs before running the above trick else at Delete statement above it will delete records with empid = 10 in detail table which you won't expect for Update.

  • Thanks for the idea vishkk47.

    But it was giving me wierd error "An explicit value for the identity column in table 'emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.", even though "set IDENTITY_INSERT emp on" was the first statement

    I had to change the query to following to make it work

    set identity_insert emp on

    insert into emp (empid,empname,designation) select 100, a.empname, a.designation from emp a where a.empid = 10

    delete emp where empid = 10

    set identity_insert emp off

  • Hi !!! To make the sentence "SET IDENTITY_INSERT TableName ON" works

    You have to write all the fields of the table in the insert sentence

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

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