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

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

Answer 2

If recreating the procedures helps, here's an easy way to generate a recreation script:

1. Right click database -> Tasks -> Generate scripts
2. On page 2 ("Choose Objects") select the stored procedures
3. On page 3 ("set scripting options") choose Advanced -> Script DROP and CREATE and set it to Script DROP and CREATE.
4. Save the script somewhere and run it

Answer 3

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.)

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.

Answer 4

I tried and was unable to replicate this on another server.

However, on my Live servers I dropped the problem database from sql 2008 and recreated it using a detach and reattach and this worked fine, without these PRIMARY KEY VIOLATION errors.

Since I wanted to keep the original database live, in fact my exact steps were:

• back up sourceDb and restore as sourceDbCopy on the same instance

• take sourceDbCopy offline

• move the sourceDbCopy files to the new server

• attach the database

• rename the database to the original name