IDENTITY_INSERT 设置为 OFF - 如何打开它? [英] IDENTITY_INSERT is set to OFF - How to turn it ON?

问题描述

我有一个已删除文件存档数据库，用于存储已删除文件的 ID，我希望管理员能够恢复该文件(以及用于链接文件的相同 ID).我不想从整个表中删除 identity_insert，因为增加一个效果很好.在我对 TBL_Content 存储过程的插入中，我有这样的东西

I have a deleted file archive database that stores the ID of the file that was deleted, I want the admin to be able to restore the file (as well as the same ID for linking files). I do not want to take identity_insert off the entire table, as the increment by one works great. In my insert to TBL_Content store procedure I have something like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET IDENTITY_INSERT tbl_content ON
GO

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 
...insert command...
SET IDENTITY_INSERT tbl_content OFF

但我一直收到同样的错误:

But I keep getting the same error:

无法为表中的标识列插入显式值当 IDENTITY_INSERT 设置为 OFF 时为TBL_Content".

Cannot insert explicit value for identity column in table 'TBL_Content' when IDENTITY_INSERT is set to OFF.

有什么帮助吗?

推荐答案

您是否应该在存储过程中将标识插入设置为 on?看起来您仅在更改存储过程时才将其设置为 on，而不是在实际调用它时.试试:

Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO

这篇关于IDENTITY_INSERT 设置为 OFF - 如何打开它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！