Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Error: Cannot update identity column


Getting Error: Cannot update identity column

Author
Message
Jeff Wood
Jeff Wood
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6774 Visits: 1911
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45040 Visits: 39894
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Wood
Jeff Wood
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6774 Visits: 1911
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. Smile

K. Brian Kelley
@‌kbriankelley
vishkk47
vishkk47
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
ranas
ranas
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
karolanet333
karolanet333
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search