Procedures using IDENTITY column fail with primary key violation after restoring sql 2000 backup onto sql 2008

Question

I've just moved a database from a SQL 2000 instance to a SQL 2008 instance and have encountered an odd problem which appears to be related to IDENTITY columns and stored procedures.

I have a number of stored procedures in the database along the lines of this

create procedure usp_add_something @somethingId int, @somethingName nvarchar(100)
with encryption
as

-- If there's an ID then update the record
if @somethingId <> -1 begin

 UPDATE  something  SET somethingName = @somethingName

end else begin

 -- Add a new record
 INSERT INTO something ( somethingName )  VALUES ( @somethingName )

end

go

These are all created as ENCRYPTED stored procedures. The id column (e.g. somethingId in this example) is an IDENTITY(1,1) with a PRIMARY KEY on it, and there are lots of rows in these tables.

Upon restoring onto the SQL 2008 instance a lot of my database seems to be working fine, but calls like

exec usp_add_something @somethingId = -1, @somethingName = 'A Name'

result in an error like this:

Violation of PRIMARY KEY constraint 'Something_PK'. Cannot insert duplicate key in object 'dbo.something'.

It seems that something is messed up that either causes SQL Server to not allocate the next IDENTITY correctly...or something like that. This is very odd!

I'm able to INSERT into the table directly without specifying the id column and it allocates an id just fine for the identity column.

There are no records with somethingId = -1 ... not that that should make any difference.

If I drop and recreate the procedure the problem goes away. But I have lots of these procedures so don't really want to do that in case I miss some or there is a customized procedure in the database that I overwrite.

Does anyone know of any known issues to do with this? (and a solution ideally!)

Is there a different way I should be moving my sql 2000 database to the sql 2008 instance? e.g. is it likely that Detach and Attach would behave differently?

I've tried recompiling the procedure using sp_recompile 'usp_add_something' but that didn't solve the problem, so I can't simply call that on all procedures.

thanks for any help

R

(cross-posted here)

Answer 1

If the problem is an improperly set identity seed, you can reset a table this way:

DBCC CHECKIDENT (TableName, RESEED, 0);
DBCC CHECKIDENT (TableName, RESEED);

This will automatically find the highest value in the table and set the seed appropriately so you don't have to do a SELECT Max() query. Now fixing the table can be done in automation, without dynamic SQL or manual script writing.

But you said you can insert to the table directly without a problem, so it's probably not the issue. But I wanted to post to set the record straight about the easy way to reset the identity seed.

Note: if your table's increment is negative, or you in the past reset the seed to use up all negative numbers starting at the lowest after consuming all the positive numbers, all bets are off. Especially in the latter case (having a positive increment, but you are using identity values lower than others already in the table), then you do not want to run DBCC CHECKIDENT without specifying NORESEED ever. Because just DBCC CHECKIDENT (TableName); will screw up your identity value. You must use DBCC CHECKIDENT (TableName, NORESEED). Fun times will ensue if you forget this. :)

Answer 2

First, check the maximum ID from your table:

select max(id_column) from YourTable

Then, check the current identity seed:

select ident_seed('YourTable')

If the current seed is lower than the maximum, reseed the table with dbcc checkident:

DBCC CHECKIDENT (YourTable, RESEED, 42)

Where 42 is the current maximum.

Demonstration code for how this can go wrong:

create table YourTable (id int identity primary key, name varchar(25))
DBCC CHECKIDENT (YourTable, RESEED, 42)
insert into YourTable (name) values ('Zaphod Beeblebrox')
DBCC CHECKIDENT (YourTable, RESEED, 41)
insert into YourTable (name) values ('Ford Prefect') --> Violation of PRIMARY KEY