May 15, 2002 at 11:30 pm
is there anyway to tell status of Identiry_Insert (On/Off) and if on the table it is set on?
something like "select @@identity_insert"
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
May 16, 2002 at 4:43 am
I dont think so. You have to set it at a session level, no way to temporily disable it for all users. So when you set it you'd keep track of it.
Andy
May 16, 2002 at 5:53 am
I have to agree with Andy, since this is set at the session level it makes it harder to see, and there is no direct variable that will give this away.
However if you are trying to check the state of it for the current connection you could cheat a bit by purposely throwing errors.
Ex.
(Contact table is same structure for this.)
CREATE TABLE [Contact2] (
[idx] [smallint] IDENTITY (1, 1) NOT NULL ,
[con] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Contact2] PRIMARY KEY CLUSTERED
(
[idx]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Contact2 (idx) VALUES (1)
GO
Error When state is off
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Contact2' when IDENTITY_INSERT is set to OFF.
SET IDENTITY_INSERT Contact2 ON
GO
INSERT INTO Contact2 (idx) VALUES (1)
Error When state is on
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'con', table 'testDb.dbo.Contact2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
or if Identity value already exists and is unique index (here is PK).
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Contact2'. Cannot insert duplicate key in object 'Contact2'.
The statement has been terminated.
If you have no other null columns then you could do like so to test when it is on.
BEGIN TRANSACTION
INSERT INTO Contact2 (idx) VALUES (100000)
ROLLBACK TRANSACTION
If you don't get an error then rollback will under insert and you know is ON.
Also if you are checking multiple tables for a connection and you find 1 then you can assume OFF for rest as ON can only be for one.
SET IDENTITY_INSERT Contact2 OFF
GO
SET IDENTITY_INSERT Contact ON
GO
SET IDENTITY_INSERT Contact2 ON
GO
Error
Server: Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'testDb.dbo.Contact'. Cannot perform SET operation for table 'Contact2'.
But I do agree you should just keep this in a varibale in the users app to know this without doing this.
Edited by - antares686 on 05/16/2002 05:54:45 AM
May 16, 2002 at 11:46 am
quote:
I have to agree with Andy, since this is set at the session level it makes it harder to see, and there is no direct variable that will give this away.However if you are trying to check the state of it for the current connection you could cheat a bit by purposely throwing errors.
Ex.
(Contact table is same structure for this.)
CREATE TABLE [Contact2] (
[idx] [smallint] IDENTITY (1, 1) NOT NULL ,
[con] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Contact2] PRIMARY KEY CLUSTERED
(
[idx]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Contact2 (idx) VALUES (1)
GO
Error When state is off
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Contact2' when IDENTITY_INSERT is set to OFF.
SET IDENTITY_INSERT Contact2 ON
GO
INSERT INTO Contact2 (idx) VALUES (1)
Error When state is on
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'con', table 'testDb.dbo.Contact2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
or if Identity value already exists and is unique index (here is PK).
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Contact2'. Cannot insert duplicate key in object 'Contact2'.
The statement has been terminated.
If you have no other null columns then you could do like so to test when it is on.
BEGIN TRANSACTION
INSERT INTO Contact2 (idx) VALUES (100000)
ROLLBACK TRANSACTION
If you don't get an error then rollback will under insert and you know is ON.
Also if you are checking multiple tables for a connection and you find 1 then you can assume OFF for rest as ON can only be for one.
SET IDENTITY_INSERT Contact2 OFF
GO
SET IDENTITY_INSERT Contact ON
GO
SET IDENTITY_INSERT Contact2 ON
GO
Error
Server: Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'testDb.dbo.Contact'. Cannot perform SET operation for table 'Contact2'.
But I do agree you should just keep this in a varibale in the users app to know this without doing this.
Edited by - antares686 on 05/16/2002 05:54:45 AM
ok thanks for the replies. regarding context - there is no "user app" per se. this functionality must work blind with user input - input which could include text like "set identity_insert
on". so we would not know ourselves whether it was coming or not.
but this feedback is greatly appreciated. thanks
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply