Cannot update identity column

I have just taken over a sqlserver 2k database using C# (not to mention I
don't know the language). I have a column in one of the tables that is an
autonumber as well as a primary key. Here is the problem, the autonumber
was created to display the current year and a number. Example, 2004001,
2004002, 2004003, etc. Now that the year has changed, the column is still
generating numbers for 2004, when I try to update the field and change a
record for example: Update ABC set rptnum = '2005001' where rptnum =
'2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
need to change the value to reflect 2005001, I also need the next record
enter to automaticly get the next number ex. 2005002, 2005003, etc.
Thanks in advance,vickie wrote:
> I have just taken over a sqlserver 2k database using C# (not to
> mention I don't know the language). I have a column in one of the
> tables that is an autonumber as well as a primary key. Here is the
> problem, the autonumber was created to display the current year and a
> number. Example, 2004001, 2004002, 2004003, etc. Now that the year
> has changed, the column is still generating numbers for 2004, when I
> try to update the field and change a record for example: Update ABC
> set rptnum = '2005001' where rptnum = '2004257' I get 'Cannot update
> identity column 'rptnum''. Not only do I need to change the value to
> reflect 2005001, I also need the next record enter to automaticly get
> the next number ex. 2005002, 2005003, etc. Thanks in advance,
AutoNumber sounds like an Access term. SQL Server uses IDENTITY
attribute on integer-type columns. THe first thing you should know about
identity columns is that they are not really designed to be intelligent
keys. There are usually used as a surrogate key, where the internal
value is meaningless.
Your inherited application is using them as intelligent keys, which I
don't think is a really good idea. Identity columns cannot be updated
directly. This is by design to prevent chaning values which SQL Server
is trying to make sure are unique.
You can update an identity by using the SET IDENTITY_INSERT ON / OFF
function before the update. In any case, you'll need to reseed the
identity value before you do this to get it to start pulling numbers
from your desired range.
You can reseed the identity using the DBCC CHECKIDENT function. See BOL
for more information on the actual parameters for the call. Reseed
first, leaving enough range for you to make the updates to the incorrect
identity values and then update the old rows. And then reconsider the
use of an identity on this table. Maybe an identity column for the PK
and a data/time column for dating would be a better idea.
David Gugick
Imceda Software
www.imceda.com|||By default, identity inserts are prohibited. However, by calling
SET IDENTITY_INSERT <table> ON/OFF
you can alter this behaviour.
So you may turn identity inserts on, insert a single new record and turn
it off again.
/Jo
vickie wrote:

> I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Hopefully, the person who designed this table got fired - or sent on a
course to learn something about SQL Server! ;-)
Using IDENTITY (the proper term for an "autonumber" column). as a
"meaningful" key is very wrong. I suggest that your best bet is to add
a DATETIME column (if you don't have one already) to represent the date
and then ignore the IDENTITY values. Set a default of the current date
on the DATETIME column. That will likely require some application
changes but the alternative of trying to control the IDENTITY value is
a very messy kludge.
The short term fix is to use DBCC CHECKIDENT to change the identity
seed value:
DBCC CHECKIDENT (YourTable, RESEED, 2005001)
Then delete and re-insert the rows that have the wrong ID (You cannot
UPDATE an IDENTITY value).
If you do resort to the kludge rather than fix the table properly then
you will obviously have the same problem again next year.
Hope this helps.
David Portas
SQL Server MVP
--|||First the preaching. The identity property of a column is not good for what
you are doing with it. It is only an effective tool for building a
surrogate key. It is not meant to be modified, so it makes a bad value to
show to users. Reconsider how you are doing this, and just make the column
an integer that you insert values into. I also assume that you probably
don't want the chance of getting gaps in the sequence either, which is
common with identities.
As for what you actually asked: You cannot (as the error says) update
identity values. You will have to recreate your table, insert the existing
values (can't be more than a thousand rows, apparently.) You can change
the seed:
dbcc checkident(<tablename>,reseed,20050000)
Here is a test script to show you it in action.
create table testIdentity
(
id int identity (2004001,1),
value varchar(10)
)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
*/
dbcc checkident(testIdentity,reseed,2005000)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
2005001 stuff
2005002 stuff
2005003 stuff
2005004 stuff
*/
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"vickie" <victoria.wheaton@.ngc.com> wrote in message
news:c6e431cc1937df694405d2d97d6d6c6e@.lo
calhost.talkaboutsoftware.com...
>I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Yeah, but the question was can you UPDATE the identity value. Which you
cannot, even with this setting set.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jo Siffert" <jo.siffert@.gmx.net> wrote in message
news:eEb4AYtCFHA.1296@.TK2MSFTNGP10.phx.gbl...
> By default, identity inserts are prohibited. However, by calling
> SET IDENTITY_INSERT <table> ON/OFF
> you can alter this behaviour.
> So you may turn identity inserts on, insert a single new record and turn
> it off again.
> /Jo
> vickie wrote:
>|||>> You can update an identity by using the SET IDENTITY_INSERT ON / OFF
Are you sure?
Identity columns are non-updateable. The only way one could alter the
identity column value is to delete and insert the same data within a single
transaction. But then, any existing references on this column could all be
messed up.
Anith|||Anith Sen wrote:
> Are you sure?
> Identity columns are non-updateable. The only way one could alter the
> identity column value is to delete and insert the same data within a
> single transaction. But then, any existing references on this column
> could all be messed up.
Oops. Talking about updates and inserts before coffee is a bad idea.
David Gugick
Imceda Software
www.imceda.com|||On 4 Feb 2005 08:33:38 -0800, David Portas wrote:
(snip)
>If you do resort to the kludge rather than fix the table properly then
>you will obviously have the same problem again next year.
Or a much bigger problem if the company fares well and more than 999 new
rows are inserted during this year......
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job.
You have all of the horse manure and probably cannot kill the guy who
screwed this up. Bummer.
Rows are not records; fields are not columns; tables are not files.
More importantly, autonumbers (IDENTITY, GUID, ROWID, et al) are not
keys by definition. They are exposed physical locators and have
nothing to do the logicl data model.
You need to start over and move the data to a new schema. If you
business logic is to use the 1950's IBM magnetic tape label codes, then
write a procedure to build them.
I also hope that your business rules are not to grow beyond 1000
transactions per year -- plan stagnation and failure rather than
letting it happen.