Mike A Mike A - 4 years ago 872
PHP Question

How to correctly use Laravel with SQL Server instead on MySQL?

I am trying to use SQL Server for my databases with my Laravel project. I was able to connect SQL Server with Laravel 5.2. However, when I try to seed data into the table I get this error

[Illuminate\Database\QueryException] SQLSTATE[23000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cann ot insert explicit value for identity column in table 'surveys' when IDENTITY_INSERT is set to OFF. (SQL: insert into [surveys] ([id], [name]) values (10, 'Some Text'))



Note: I am trying to supply the identity value which is probably what is causing the problem.


While researching the SQL error, I learned that I need to execute the following queries.

Before seeding I need to execute

SET IDENTITY_INSERT surveys ON;


After the seeding I need to execute

SET IDENTITY_INSERT surveys OFF;


But I am not sure how can I execute these command using Laravel

How can I seed while supplying the value for the identity column without this issue?

UPDATED
Here is my seeder

<?php

use Illuminate\Database\Seeder;

class FinalSurveyTS extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$myTable = 'surveys';

DB::statement('SET IDENTITY_INSERT ' . $myTable . ' ON');


DB::table($myTable)->truncate();


DB::table($myTable)->insert([
'id' => 10,
'name' => 'some name',
]);

DB::statement('SET IDENTITY_INSERT ' . $myTable . ' OFF');
}
}

Answer Source

In order to execute those commands, you can do it as raw

DB::statement('SET IDENTITY_INSERT surveys ON;');

and then

DB::statement('SET IDENTITY_INSERT surveys OFF;');

DB::statement is supposed for commands that don't return data

Edit When the seeder is executed like this:

DB::statement(...);
DB::table(...)->insert(...);
DB::statement(...);

Seems that for some reason the state of IDENTITY_INSERT variable is not kept, so a way to make it work is to wrap that code in a transaction.

try {
    DB::beginTransaction();
    DB::statement(...);
    DB::table(...)->insert(...);
    DB::statement(...);
    DB::commit();
} catch(Exception $e) {
    DB::rollback();
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download