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.
Best Answer
This is a session setting; there is nothing to roll back. If you had issued
SET DATEFORMAT DMY;
orSET LANGUAGE FRENCH;
, would you expect an error to revert you toMDY
orenglish_us
? What if you hadSET SHOWPLAN
on, would you expect a rollback to stop showing you plans on subsequent queries?To be fair, the
IDENTITY_INSERT
case is a little special, since it's the only session setting I can think of off the top of my head that interacts directly with a table. But it's still applicable only to your session. The thinking is probably that you could go on to insert many rows in multiple batches, even after an error has been raised (as long as it hasn't severed the connection).As an aside, you're using SQL Server 2012, so why are you using SQL Server 7.0 error handling techniques? You could use
TRY/CATCH
instead ofSET XACT_ABORT
and then turn the setting off in theCATCH
if it's on (which you can check). For a good primer on error handling, see Erland Sommarskog's articles on Error and Transaction Handling in SQL Server: