SQL Server - MSSQL 2000 IDENTITY_INSERT is set to OFF

Asked By Davaldi on 08-Jan-11 04:11 AM

Hello,

I am having serious problems with

Warning: mssql_query() [https://secure.mxpgaming.org/topo4/function.mssql-query]: message: Cannot insert explicit value for identity column in table 'account_login' when IDENTITY_INSERT is set to OFF

But IDENTITY_INSERT is actually ON on dbo.account_login " ID " table.

Permissions also are active for the table.

Which could be the resolution for this problem?

( i get same error using mssql2005 )

Reena Jain replied to Davaldi on 08-Jan-11 04:23 AM

hi,

This error comes when we have a Identity Specification is ‘Yes’ and IsIdentity is also ‘Yes’, Identity Increament is set to(1/2/…).

So you are unable to insert this type of row, because sql know that the Test_Id is the Identity_Column and so you cannot insert the value which already inserted. So to resolve this error, you have to set the Column_Identity ON by using the following syntax.

SET IDENTITY_INSERT tblOrderItemStatus ON

Then try to insert the row using the above same query, which was as following:

Insert Into tblTest(Test_Id,Test_Name) values(2, “TestTemp2″)

Now you need to reset the Column_Identity OFF. You can do it just by the following syntax:

SET IDENTITY_INSERT tblOrderItemStatus OFF

Hope this will help you

Web Star replied to Davaldi on 08-Jan-11 04:25 AM

Most of the time this error occurs when you try to insert any value for the identity column. It will be generated so make sure you are not try to insert in identity column

and if not insert in identity column than run this command in query analyser
SET IDENTITY_INSERT products ON
GO
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, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is already set to ON so if you will get that message means it set to ON other wise it will set to ON after execution

Anoop S replied to Davaldi on 08-Jan-11 04:32 AM

why are you inserting the value for identity column when u have declare as an identity. Except that column insert all values. Leave that column. If u want to insert it manually then make stored procedure for this insert statement and within it declare SET IDENTITY_INSERT table_name ON and then give your insert query within exec(insert_query).

Davaldi replied to Davaldi on 08-Jan-11 05:20 AM

I've done some changements, but in any case i still get this error after i submit the registration

Warning: mssql_query() [https://secure.mxpgaming.org/topo/register/function.mssql-query]: message: Cannot insert the value NULL into column 'id', table 'AccountServer1.dbo.account_login'; column does not allow nulls. INSERT fails. (severity 16) in C:\xampp\https\secure\topo\register\config.php on line 15

Warning: mssql_query() [https://secure.mxpgaming.org/topo/register/function.mssql-query]: Query failed in C:\xampp\https\secure\topo\register\config.php on line 15

Config from line 1 to 16

<?

$dbHost = '127.0.0.1';
$dbUser = '';
$dbPass = '';
$dbName = 'AccountServer1';
$chinese_jobs = true;

$connection = mssql_connect ($dbHost, $dbUser, $dbPass) or die ('MsSQL connect failed. ' . mssql_error());
mssql_select_db($dbName) or die('Cannot select database. ' . mssql_error());

function do_query($query,$database)
   {
      mssql_select_db($database) OR DIE("Can't find the database!!");
      return mssql_query($query);
   }

Daivagna Nanavati replied to Davaldi on 08-Jan-11 07:28 AM

Hi Davaldi

Here what your error suggests is you are trying to insert the value where null is not permitted so you have two options

1) If there is Id column in your table and if its primary key than it has to be auto incremented and in that case do not try to insert data in it from your query, instead only insert fields excluding id field and make it auto increment and identity seed to one

2) or if you want to insert data in that column than do not put any constraint on that column and make it allow null so that null values can also be inserted abut this scenario is generally not advisable on id columns

let me know

Thanks

Davaldi replied to Daivagna Nanavati on 08-Jan-11 07:39 AM

I fixed the problem, setting IDENTITY_INSERT to off

And building a different mssql connection\registration script that uses better syntax

Thanks for the help guys, usefull.