sql-server - 将 sql 2000 备份恢复到 sql 2008 后，使用 IDENTITY 列的过程因主键冲突而失败

Question

我刚刚将数据库从 SQL 2000 实例移至 SQL 2008 实例，并遇到了一个奇怪的问题，该问题似乎与 IDENTITY 列和存储过程有关。

我在数据库中有许多与此类似的存储过程

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

这些都是作为加密存储过程创建的。 id 列(例如本例中的 SomethingId)是一个带有主键的 IDENTITY(1,1)，这些表中有很多行。

恢复到 SQL 2008 实例后，我的很多数据库似乎工作正常，但调用如下

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

导致这样的错误:

违反主键约束“Something_PK”。无法在对象“dbo.something”中插入重复的键。

似乎有些事情搞砸了，导致 SQL Server 无法正确分配下一个 IDENTITY...或者类似的事情。这很奇怪!

我可以直接 INSERT 到表中，而无需指定 id 列，并且它会为标识列分配一个 id。

没有 SomethingId = -1 的记录...但这不会有任何区别。

如果我删除并重新创建该过程，问题就会消失。但我有很多这样的过程，所以不想这样做，以防万一我错过了一些过程，或者数据库中有一个自定义过程被我覆盖。

有人知道与此相关的任何已知问题吗？ (以及理想的解决方案!)

是否有其他方法可以将我的 sql 2000 数据库移动到 sql 2008 实例？例如Detach 和 Attach 的行为是否可能不同？

我尝试使用 sp_recompile 'usp_add_something' 重新编译该过程，但这并没有解决问题，所以我不能简单地在所有过程上调用它。

感谢您的帮助

R

(交叉发布 here )

Answer 1

如果问题是身份种子设置不正确，您可以通过以下方式重置表:

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

这将自动查找表中的最高值并适当设置种子，因此您不必执行 SELECT Max()询问。现在修复表可以自动化完成，无需动态 SQL 或手动编写脚本。

但是你说你可以直接插入到表中没有问题，所以这可能不是问题。但我想发帖澄清重置身份种子的简单方法。

注意:如果您的牌 table 增量为负数，或者您过去重置种子以在消耗完所有正数后从最低处开始用完所有负数，所有投注均无效关闭。。特别是在后一种情况下(具有正增量，但您使用的标识值低于表中已有的其他值)，那么您不想运行 DBCC CHECKIDENT未指定NORESEED 曾经。因为只是DBCC CHECKIDENT (TableName);会搞砸你的身份值(value)。您必须使用DBCC CHECKIDENT (TableName, NORESEED) 。如果你忘记了这一点，有趣的时光就会随之而来。 :)