BUG: Execute Statement Behaves Incorrectly with Set Identity Insert On (281200)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbpending KB281200 |
|---|
|