November 30, 2012 at 7:52 am
I just came across something weird, and was wondering if anyone knows about this.
I have two structurally similar (if not identical) staging databases on the same server (let's call them "DB-1" and "DB-2"). I tried running an INSERT script on a table (the same table) in each database, and saw something weird.
My insert includes an identity insert, so I wanted to SET IDENTITY_INSERT on the table.
I went into DB-1, ran my SET IDENTITY_INSERT, and ran my INSERT query. It ran with no problem.
I then changed to DB-2 and did exactly the same thing, but it failed. I couldn't find any reason for it to fail, and it stumped me, until I took a closer look at the error message (note: names changed to protect the innocent):
Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'DB-1.dbo.myTable'. Cannot perform SET operation for table 'myTable'.
Huh?!? I switched to DB-2. Why are you telling me you're trying to set the table in DB-1?!? WTF?!?
I'd used a manual switch (from my DB drop-down). I also tried inserting a USE DB-2 command. No dice.
I finally got it to work by disconnecting and reconnecting to the database, but this issue is still bothering me.
Has anyone else come across this?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 30, 2012 at 7:57 am
just to rule out the obvious are you using a 3 part naming convention in the insert statement?
---------------------------------------------------------------------
November 30, 2012 at 7:58 am
I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on for one table at a time.
So the solution is: set identity_insert off after inserting the data into the first table. Then you can set it on for another table.
set identity_insert dbo.MyTable1 on;
insert into dbo.MyTable1 ...
set identity_insert dbo.MyTable1 off;
set identity_insert dbo.MyTable2 on;
insert into dbo.MyTable2....
set identity_insert dbo.MyTable2 off;
November 30, 2012 at 7:59 am
george sibbald (11/30/2012)
just to rule out the obvious are you using a 3 part naming convention in the insert statement?
Originally, no, but when I came across this problem, I tried using it in my SET IDENTITY_INSERT statement. It didn't make a difference.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 30, 2012 at 8:00 am
u may have a USE database at the very top of ur script. I've done that before when not totally paying attention
November 30, 2012 at 8:02 am
BaldingLoopMan (11/30/2012)
u may have a USE database at the very top of ur script. I've done that before when not totally paying attention
I did not have anything in my original script that specified what database to use (I did that intentionally). When I came across this error, I added the USE statement. It did not make a difference.
My colleague mentioned that this might be a bug in 2008.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 30, 2012 at 8:08 am
from http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
---------------------------------------------------------------------
November 30, 2012 at 8:10 am
This really sounds familiar to me 😀
WolfgangE (11/30/2012)
I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on for one table at a time.So the solution is: set identity_insert off after inserting the data into the first table. Then you can set it on for another table.
November 30, 2012 at 8:11 am
WolfgangE (11/30/2012)
I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on for one table at a time.So the solution is: set identity_insert off after inserting the data into the first table. Then you can set it on for another table.
set identity_insert dbo.MyTable1 on;
insert into dbo.MyTable1 ...
set identity_insert dbo.MyTable1 off;
set identity_insert dbo.MyTable2 on;
insert into dbo.MyTable2....
set identity_insert dbo.MyTable2 off;
Unfortunately, this is not my scenario.
I am not setting two different tables on the same database.
I am trying to set a structurally identical table on two different databases.
The issue is when I change from one database to another.
Not sure if this still solution still applies.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 30, 2012 at 8:12 am
george sibbald (11/30/2012)
from http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspxAt any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
Okay, this sounds like it might make more sense. I'm trying to change databases in the same session.
Next time, I'll try using different sessions and see if it makes a difference.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 30, 2012 at 8:15 am
Thanks, everyone, for your feedback, BTW.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 30, 2012 at 8:15 am
WolfgangE (11/30/2012)
This really sounds familiar to me 😀WolfgangE (11/30/2012)
I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on for one table at a time.So the solution is: set identity_insert off after inserting the data into the first table. Then you can set it on for another table.
If you change from one database to another in the same session then I believe wolfgangs answer still holds.
November 30, 2012 at 8:18 am
OTF (11/30/2012)
WolfgangE (11/30/2012)
This really sounds familiar to me 😀WolfgangE (11/30/2012)
I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on for one table at a time.So the solution is: set identity_insert off after inserting the data into the first table. Then you can set it on for another table.
If you change from one database to another in the same session then I believe wolfgangs answer still holds.
Yeah, like I said earlier, the fact that it's the same session does make sense. But I still think it's weird that it would do that.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply