将 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

（交叉发布 此处）

Answer 1

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

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

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

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

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

Answer 2

首先，检查表中的最大 ID：

select max(id_column) from YourTable

然后，检查当前身份种子：

select ident_seed('YourTable')

如果当前种子低于最大值，请使用 dbcc checkident 为表重新设定种子：

DBCC CHECKIDENT (YourTable, RESEED, 42)

其中 42 是当前最大值。

有关如何出错的演示代码：

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 3

我尝试过，但无法在另一台服务器上复制此内容。

然而，在我的实时服务器上，我从 sql 2008 中删除了有问题的数据库，并使用分离和重新附加重新创建了它，这工作很好，没有这些主键违规错误。

由于我想让原始数据库保持活动状态，实际上我的具体步骤是：

• 在同一实例上备份 sourceDb 并恢复为 sourceDbCopy

• take sourceDbCopy 离线

• 将sourceDbCopy 文件移动到新服务器

• 附加数据库

• 重命名数据库改为原来的名称

Answer 4

如果重新创建程序有帮助，这里有一个生成重新创建脚本的简单方法：

1. 右键单击数据库 ->任务->生成脚本
2. 在第 2 页（“选择对象”）上选择存储过程
3. 在第 3 页（“设置脚本选项”）上选择“高级”->“存储过程”编写 DROP 和 CREATE 脚本并将其设置为 Script DROP and CREATE。
4. 将脚本保存在某处并运行