Paul Randal recently tweeted following: "Hmm - only way I can figure out to see what current session IDENTITY_INSERT is set to is to force a stackdump on the connection. Not nice."
Possibly Quick and Dirty Step-In-The-Right-Direction? Maybe?
SET NOCOUNT ON;
DECLARE @IdentityInsertValue varchar(3);
SET @IdentityInsertValue = 'ON';
CREATE TABLE #t (a int identity(1,1));
-- change ON/OFF value to test
SET IDENTITY_INSERT #t OFF;
BEGIN TRY
INSERT #T (A) VALUES (42); -- Nod to Douglas Adams
END TRY
BEGIN CATCH
SET @IdentityInsertValue = 'OFF';
END CATCH
PRINT @IdentityInsertValue;
DROP TABLE #T
Update
Following is based upon an inoccuous clause in the SQL Server BOL about SET IDENTITY_INSERT located
here:
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, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for. This code is definitely a kludge but provides the information that Paul needed, I believe. Probably not the best solution. Surprised that there is no direct way to determine on a per table basis if the IDENTITY_INSERT flag is on or off for a given session.
An interesting thing is that there appears to be a bug in the error message generated. The schema name appears to always be dbo.
SET NOCOUNT ON;
USE AdventureWorks;
GO
SET IDENTITY_INSERT Person.Contact ON;
-- following starts the test code.
DECLARE @ErrMsg nvarchar(2048)
SET @ErrMsg = NULL;
CREATE TABLE #t (a int identity(1,1));
BEGIN TRY
SET IDENTITY_INSERT #t ON;
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE();
END CATCH
DROP TABLE #t;
-- definitely love the following kludge here!
SET @ErrMsg = REPLACE(@ErrMsg, 'IDENTITY_INSERT is already ON for table ''', '');
set @ErrMsg = REPLACE(@ErrMsg, '''. Cannot perform SET operation for table ''#t''.', '');
PRINT @ErrMsg;
Update #2
Reported bug to Microsoft Connect
here.