Getting Error: Cannot update identity column

Question

Getting Error: Cannot update identity column

Jeff Wood

Old Hand

Points: 360
More actions
November 16, 2007 at 6:53 pm

#121116

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

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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)

Jeff Wood Old Hand Points: 360 More actions · Answer 3

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

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

vishkk47 SSC Enthusiast Points: 127 More actions · Answer 5

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.

ranas SSC Rookie Points: 27 More actions · Answer 6

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

karolanet333 SSC Rookie Points: 25 More actions · Answer 7

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