Search code examples
sql-serversql-server-2008sql-server-2000

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

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)

Solution

  • 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