October 25, 2007 at 2:23 pm
I'm trying to set IDENTITY_INSERT OFF in a stored procedure and it doesn't work!
Here's the sample script
CREATE TABLE #table1(ID INT IDENTITY(1,1))
CREATE TABLE #table2(ID INT IDENTITY(1,1))
SET IDENTITY_INSERT #table1 ON
GO
if exists (select * from dbo.sysobjects where id = object_id('InsertIdentityOff'))
DROP PROCEDURE InsertIdentityOff
GO
CREATE PROCEDURE InsertIdentityOff(@tableName NVARCHAR(50))
AS
DECLARE @cmd NVARCHAR(512)
BEGIN
SET @cmd = 'SET IDENTITY_INSERT ' + @tableName + ' OFF'
PRINT @cmd
EXEC sp_executesql @cmd
END
GO
PRINT 'This will, as expected, throw an error'
SET IDENTITY_INSERT #table2 ON
GO
EXEC InsertIdentityOff N'#table1' --no errors
PRINT 'This will, unexpectedly, throw an error!'
SET IDENTITY_INSERT #table2 ON
GO
SET IDENTITY_INSERT #table1 OFF
PRINT 'This will not throw an error'
SET IDENTITY_INSERT #table2 ON
GO
PRINT 'Clean up'
SET IDENTITY_INSERT #table2 OFF
The output is:
This will, as expected, throw an error
Server: Msg 8107, Level 16, State 1, Line 3
IDENTITY_INSERT is already ON for table 'tempdb.dbo.#table1_____________________________________________________________________________________________________________000000000052'. Cannot perform SET operation for table '#table2'.
SET IDENTITY_INSERT #table1 OFF
This will, unexpectedly, throw an error!
Server: Msg 8107, Level 16, State 1, Line 4
IDENTITY_INSERT is already ON for table 'tempdb.dbo.#table1_____________________________________________________________________________________________________________000000000052'. Cannot perform SET operation for table '#table2'.
This will not throw an error
Clean up
On a related note, is there a way to programmatically find out which table has IDENTITY_INSERT set ON?
October 25, 2007 at 2:36 pm
IDENTITY_INSERT can only be on for one table at a time. You are turning it on but never turning it off.
CREATE PROCEDURE usp_IdentityTest
AS
CREATE TABLE #table1(ID INT IDENTITY(1,1))
CREATE TABLE #table2(ID INT IDENTITY(1,1))
SET IDENTITY_INSERT #table1 ON
INSERT INTO #table1(id) values(3)
SET IDENTITY_INSERT #table1 OFF
SET IDENTITY_INSERT #table2 ON
INSERT INTO #table2(id) values(100)
SET IDENTITY_INSERT #table2 OFF
SELECT * FROM #table1
SELECT * FROM #table2
RETURN
GO
EXEC usp_identityTest
Good luck
Daryl
October 25, 2007 at 2:45 pm
Daryl Smith (10/25/2007)
IDENTITY_INSERT can only be on for one table at a time. You are turning it on but never turning it off.
That's obvious.
I'm trying to write a generic stored procedure that will take a table name as a parameter and attempt to turn IDENTITY_INSERT OFF on it. But somehow the dynamic SQL call succeeds, but does nothing, even though when I execute the same SQL directly, it works. Please review the PRINT statements in my script to understand what I'm talking about.
October 25, 2007 at 3:05 pm
OY! RTFPost...
only one table in a session can have the IDENTITY_INSERT property set to ON.
Running the set identity_insert in the sproc is residing in a different session.
October 25, 2007 at 3:41 pm
Running the set identity_insert in the sproc is residing in a different session.
Is there any way to force the SP to run the set identity_insert command in the same session as the calling code?
October 25, 2007 at 6:30 pm
Why not include it into calling code?
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy