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:
Cannot insert explicit value for identity column in table
'TBL_Content' when IDENTITY_INSERT is set to OFF.
Any help?
Best Answer
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: