SET IDENTITY_INSERT OFF in a Stored Procedure?

  • 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?

  • 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

  • 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.

  • 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.

  • 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?

  • Why not include it into calling code?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply