I need to insert values into an identity field, but I don't know what the normal status of IDENTITY_INSERT is for this database. Is there any way to determine the status of IDENTITY_INSERT prior to running the INSERT statement?
I don't think there is. When you set identity_insert on, it is only on "for the session". This means that if you disconnect your session and then re-connect, identity_insert will not be on for any of your tables.
Since you are having this problem, I can only assume it's because you attempted to set it on and it failed. Whenever I need to use identity_insert, I always approach it the same way that I use transactions. Every transaction has a definite starting point (begin transaction) and a definite ending point (commit or rollback transaction).
Basically, whenever you set identity_insert on, you should always set it off. This will save you this kind of trouble.
-George
Microsoft SQL Server MVP My Blogs SQLCop twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.