Microsoft KB Archive/234521

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

Article ID: 234521

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q234521



BUG #: 55534 (SQLBUG_70)



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 following conditions produce this error:
 * 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 7.0.



MORE INFORMATION
To reproduce this problem, follow these steps:   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   Execute the procedure more than once by running the following code: EXEC ProcTemp 

Additional query words: Temp Table

Keywords: kbbug kbpending KB234521

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.