I have a large script for setting up a new database that does lots of identity inserts on many tables; while I'm working on extending it I quite often have transient errors due to it being a work in progress. If one of these errors is between
set identity_insert [table] on and
set identity_insert [table] off the script refuses to run a second time with the following error:
Msg 8107, Level 16, State 1, Line 15
IDENTITY_INSERT is already ON for table 'x'. Cannot perform SET operation for table 'y'.
This is a pain! To work around it I highlight the 'off' line and run that manually, or kill the connection and reconnect (identity insert is per session I gather).
I have the whole script wrapped in a transaction, with
set xact_abort on at the top so my expectation would be for the server to return to the pre-error state.
Why is this not the case? Am I doing anything wrong, and are there any more robust approaches than what I have here?
The following is a minimal test-case to reproduce the behavior. Run it twice and notice the error changes from a type conversion error in the first run to the identity_insert error on subsequent runs:
-- setup test tables for convenience if not exists (select * from sys.tables where name = 'bill') begin create table bill (id int identity primary key); end if not exists (select * from sys.tables where name = 'ben') begin create table ben (id int identity primary key, height int); end set xact_abort on -- automatically rollback transaction on error begin tran print 'adding bill records' set identity_insert bill on; -- breaks unexpectedly on *second* run insert into bill (id) values (1); set identity_insert bill off; print 'adding ben records' set identity_insert ben on; select * from ben -- insert that intentionally causes error due to type conversion failure insert into ben (id, height) values (1, 'not an int'); set identity_insert ben off; -- never gets run, as expected commit
Output from first run (expected behaviour):
adding bill records
adding ben records
Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the varchar value 'not an int' to data type int.
Output from second run (unexpected behaviour):
adding bill records
Msg 8107, Level 16, State 1, Line 14
IDENTITY_INSERT is already ON for table 'data-test.dbo.ben'. Cannot perform SET operation for table 'bill'.
SELECT @@version gives:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
I've tried this both in management studio and in visual studio just to check it's not an odd client bug.