Microsoft KB Archive/281200

= BUG: Execute Statement Behaves Incorrectly with Set Identity Insert On =

Article ID: 281200

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q281200



BUG #: 19059 (SQLBUG_65)



SYMPTOMS
A SET IDENTITY_INSERT statement on a table within an EXECUTE statement is taking effect beyond the scope of the EXECUTE statement. The SET IDENTITY_INSERT statement is working as though it is inside the EXECUTE statement. The SET IDENTITY_INSERT statement should only work within an Execute statement. For example: CREATE TABLE test (id int IDENTITY) go

EXEC ('SET IDENTITY_INSERT test ON INSERT INTO test(id) VALUES(1)') go

INSERT INTO test(id) VALUES(2) go The two INSERT statements both succeed in adding rows. However, the INSERT statement outside of the EXECUTE statement should fail with this error message:

Msg 544, Level 16, State 1 Attempting to insert explicit value for identity column in table 'test' when IDENTITY_INSERT is set to OFF



WORKAROUND
Set IDENTITY_INSERT off before the end of the statements in the EXECUTE statement. For example: EXEC ('SET IDENTITY_INSERT test ON INSERT INTO test(id) VALUES(1) SET IDENTITY_INSERT test OFF')



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.

Additional query words: exec identity insert

Keywords: kbbug kbpending KB281200

-

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

© Microsoft Corporation. All rights reserved.