IDENTITY_INSERT is set to OFF error

Question

I am trying to insert values in mssql db via php. I am getting an error Cannot insert explicit value for identity column in when IDENTITY_INSERT is set to OFF.

I have added query line to set IDENTITY_INSERT but still get same error.

Code I am using to insert values in mssql db.

   $serverName = "xxxx\SQLEXPRESS"; //serverName\instanceName
   $connectionInfo = array( "Database"=>"xxxx", "UID"=>"xxx", "PWD"=>"xxxx" , "CharacterSet" => "UTF-8");
   $con = sqlsrv_connect( $serverName, $connectionInfo);

   if($con) {
      echo "Connection established";
      }
   else{
        echo "Connection could not be established.";
        die( print_r( sqlsrv_errors(), true));
      }
    $query = "SET IDENTITY_INSERT dbo.xxx ON ";

    $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));
    }

Error I get:

Array ( [0] => Array ( [0] => 23000 [SQLSTATE] => 23000 [1] => 544 [code] => 544 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'FKNarudzbeKupacaZaglavlje' when IDENTITY_INSERT is set to OFF. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'FKNarudzbeKupacaZaglavlje' when IDENTITY_INSERT is set to OFF. ) )

Could someone tell me how to solve this issue or give advice?

You need to set identity insert to on. Hope this link would help you. http://msdn.microsoft.com/en-us/library/ms188059.aspx — Ram Grandhi, Jul 28 '15 at 11:09
But aren't you overwriting the variable `$query` with your actual query? You haven't actually run the SET IDENTITY_INSERT statement - you either need to concatenate the strings, or execute the query inbetween (do not forget to turn identity insert off again afterwards!) — Bridge, Jul 28 '15 at 11:13
Make sure to turn it back off again too or you going to have lots of challenges because you can't have that turned on for more than 1 table at a time. — Sean Lange, Jul 28 '15 at 13:19
possible duplicate of [IDENTITY\_INSERT is set to OFF - How to turn it ON?](http://stackoverflow.com/questions/3112579/identity-insert-is-set-to-off-how-to-turn-it-on) — Tanner, Jul 28 '15 at 13:27

score 2 · Accepted Answer · edited Aug 06 '15 at 09:38

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));
}

IDENTITY_INSERT is set to OFF error

1 Answers1