I was editing some script in SQL Server Management Studio.
And interacted with error:
How did I come to this error?
That script looked like:
Failure occurred on one of inserts, because one of the columns was missed. After that I added column to fix problem, but I started getting this error: IDENTITY_INSERT is already ON for table [Table_Name].
I tried to run inserts independently but still the same.
Why it happened?
IDENTITY_INSERT works in scope of one session.
And interacted with error:
IDENTITY_INSERT is already ON for table [Table_Name]
How did I come to this error?
That script looked like:
SET IDENTITY_INSERT [TABLE_NAME] ON
--insert statements
--failure on one of inserts because of not existing column
SET IDENTITY_INSERT [TABLE_NAME] ON
--insert statements
--failure on one of inserts because of not existing column
SET IDENTITY_INSERT [TABLE_NAME] ON
Failure occurred on one of inserts, because one of the columns was missed. After that I added column to fix problem, but I started getting this error: IDENTITY_INSERT is already ON for table [Table_Name].
I tried to run inserts independently but still the same.
Why it happened?
IDENTITY_INSERT works in scope of one session.
Subscribe in a reader
Subscribe via email
Thanks for this. I had a maddening problem with this of there being only one IDENTITY_INSERT statement. I could then run SET IDENTITY_INSERT table OFF followed by SET IDENTITY_INSERT table ON and it would still fail.
ReplyDeleteClosing and re-opening the connection worked nicely.
Ian
Genuinely very helpful tip, thanks
ReplyDelete- Mike
Yep, worked for me also - thanks very much!
ReplyDeleteThanks, it is really helpful tip.
ReplyDelete