BUG: Inserting Data into Temporary Table's Identity Column in Stored Procedure Fails

• Microsoft SQL Server version 7.0

SYMPTOMS

If you run a stored procedure that inserts data into a temporary table that contains an identity column, the second and all subsequent executions of the procedure fail with the following error:

Server: Msg 544, Level 16, State 1
Cannot insert explicit value for identity column in table 'objid' when IDENTITY_INSERT is set to OFF

• The stored procedure must be inserting data into the Identity column.



• The table involved must be a temporary table. If the table created in the store procedure is a permanent table, it will not fail.

WORKAROUND

Use any one of the following workarounds:

• Create the stored procedure using WITH RECOMPILE.



• Run the stored procedure using WITH RECOMPILE.



• Use a permanent table instead of a temporary table.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

To reproduce this problem, follow these steps:

1. Run this code to create the procedure in any database:

   
   If exists(select * from sysobjects where name = 'ProcTemp')
   	DROP PROCEDURE ProcTemp
   GO
   
   CREATE PROCEDURE ProcTemp
   AS
        CREATE TABLE #TableTemp
        (IDColumn        int         IDENTITY(1, 1) NOT NULL,
            OtherColumn     char(3)     NULL)
   
        SET IDENTITY_INSERT #TableTemp ON
   
        INSERT
            INTO        #TableTemp
                        (IDColumn, OtherColumn)
            VALUES      (7298435,  "jhd")
   
       SET IDENTITY_INSERT #TableTemp OFF
   GO 




2. Execute the procedure more than once by running the following code:

   
   EXEC ProcTemp 

Additional query words:

Keywords : SSrvStProc kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug