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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply