Identity_Insert Is Set to Off - How to Turn It On

IDENTITY_INSERT is set to OFF - How to turn it ON?

Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

IDENTITY_INSERT is set to OFF?

You are trying to assign an explicit value for the identity column instead of leaving database create one to you when you are inserting data.

You are doing something like:

insert into MyTable (Id, Code, Name, dob) VALUES (1, 'XXX', 'xxx', 'xxx', somedate)

instead of

insert into MyTable(Code, Name, dob) VALUES ('xxx', 'xxx', somedate)

IDENTITY_INSERT is set to OFF error

I am posting the solution in case someone faces a similar problem:

$query2 = "SET IDENTITY_INSERT dbo.FKNarudzbeKupacaZaglavlje ON ";

$stmt2 = sqlsrv_query( $con, $query2);

if( $stmt2=== false ) {
die( print_r( sqlsrv_errors(), true));
}

$query = "INSERT INTO dbo.xxx([Id],[IdFirma], [VrstaDokumenta], [BrojDokumenta], [BrojDokumentaKroz],[DatumDokumenta], [IdKupac], [VrstaCijene], [IdKorisnik], [NacinPlacanja], [DatumZadnjeAkcije], [Status], [StatusArhive], [StatusIzmjene], [StatusStampe], [VrstaFakture]) 
          VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

$params = array($id,$IdFirma,$VrstaDokumenta,$BrojDokumenta,
                $BrojDokumentaKroz, $DatumDokumenta, $IdKupac, $VrstaCijene, $IdKorisnik, 
                $NacinPlacanja, $DatumZadnjeAkcije, $Status, $StatusArhive, $StatusIzmjene, $StatusStampe, $VrstaFakture);    

/* Prepare and execute the statement. */

$stmt = sqlsrv_query( $con, $query, $params);

if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
}

Is identity_insert reset to OFF at session close?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a6ba2bb-5e82-47e8-a1a0-16fc044b951e/detect-current-identityinsert-settings?forum=transactsql

From Microsoft:

Since SET IDENTITY_INSERT is a session sensitive, it is managed in buffer level without storing somewhere. This means we do not need to check the IDENTITY_INSERT status as we never use this key word in current session.

If there is anything unclear, please feel free to ask.

Thanks,

Jin Chen - MSFT

How to enable Identity_insert on SQL Server Level

You don't. The documentation confirms this:

SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table.

...

Remarks

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.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Emphasis mine.

If you need to enable it on multiple tables, you must enable it on one table, disable it, then en able it on the next, etc, etc.

ASP.NET: Identity_Insert set to OFF - How do I fix this?

This is not specifically an ASP.NET issue, it's a SQL error.

When you have an identity column, every time a new row is added, that column should contain a new value (normally incremented by 1).

The error your showing is because either you or the technology you're using is attempting to tell SQL Server what value should be in that identity column.

You have the following options available to you (that I'm aware of)...

If you wrote the SQL query and listed all the columns then you should remove the identity column. For instance...

INSERT INTO [MYTABLE] ([IDENTCOLUMN],[MYVAL]) VALUES (1,1)

Should become...

INSERT INTO [MYTABLE] ([MYVAL]) VALUES (1)

If you wrote the SQL query and did NOT list all the columns then you should write all the columns without the identity column. For instance...

INSERT INTO [MYTABLE] VALUES (1,1)

Should become...

INSERT INTO [MYTABLE] ([MYVAL]) VALUES (1)

Otherwise, if you actually want to specifically set the value of the identity column you need to tell SQL Server that you're overriding the normal identity processing by using the following...

SET IDENTITY_INSERT [MYTABLE] ON
INSERT INTO [MYTABLE] ([IDENTCOLUMN],[MYVAL]) VALUES (1,1)
SET IDENTITY_INSERT [MYTABLE] OFF

Turn off IDENTITY_INSERT for Dataset insert

You have the options mixed up:

SET IDENTITY_INSERT orders ON

will turn ON the ability to insert specific values (that you specify) into a table with an IDENTITY column.

SET IDENTITY_INSERT orders OFF

Turns that behavior OFF again and the normal behavior (you can't specify values for IDENTITY columns since they are auto-generated) is reinstated.

Marc

---

Related Topics