Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Error: Cannot update identity column Expand / Collapse
Author
Message
Posted Friday, November 16, 2007 6:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 30, 2010 3:57 PM
Points: 12, Visits: 17
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






Post #423276
Posted Friday, November 16, 2007 8:02 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #423281
Posted Friday, November 16, 2007 11:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #423295
Posted Sunday, November 18, 2007 5:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 30, 2010 3:57 PM
Points: 12, Visits: 17
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
Post #423407
Posted Monday, November 19, 2007 6:11 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #423547
Posted Thursday, October 8, 2009 3:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 11:46 AM
Points: 11, Visits: 12
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.
Post #799825
Posted Friday, May 21, 2010 3:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 21, 2010 3:21 PM
Points: 1, Visits: 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
Post #926302
Posted Thursday, December 2, 2010 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:48 AM
Points: 1, Visits: 0
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
Post #1029325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse