Eloquent does not process transaction, rather it execute one statement at a time when using SQL Server

[malhayek2014]

I am having problems with using SQL Server on Laravel 5.2.

Here are the problems that I found

I encounter the first problem when seeding tables where my identity column is preset.

    $myTable = 'surveys';

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

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

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

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

What happens here is that the DB::statement() part is executed separately. Hopping to fix the problem using Eloquent, I wrapped my code in a transaction like so

    try {
            DB::beginTransaction();

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

            DB::table($myTable)->insert([
             'id' => 10,
             'name' => 'AHOLD IMAGE TRACKING STUDY',
    ]);

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

            DB::commit();

    } catch(Exception $e) {

        echo $e->getMessage();  //for testing
        DB::rollback();
    }

The problem here is that the code in the DB::statement() block is not executed with in the transaction. In fact, it does not seems to be starting transaction at all.

For my code to work I will have to do something nasty like this

    try {
        DB::statement(' BEGIN TRAN T1;

                        SET IDENTITY_INSERT '. $myTable .' ON;


                        INSERT INTO ' . $myTable . ' (id, name) VALUES(10, \'AHOLD IMAGE TRACKING STUDY\'); 


                        SET IDENTITY_INSERT '. $myTable .' OFF;

                        COMMIT TRAN T1;');


    } catch(Exception $e) {

        echo $e->getMessage();

    }

This is causing me a big problem when using SQL Server.

Another issue with SQL Server is that when creating a schema the string length have a max value of 4000.

so running this migration will give me an error so I have to change my column length from 5000 to 4000. I don't know if this should automaticly be handled by the ORM or I should use a different column type.

    Schema::create('surveys', function(Blueprint $table)
    {
        $table->increments('id')->unsigned();
        $table->string('name', 120);
                    $table->string('description', 5000);
        $table->enum('status', ['Active','Inactive'])->default('Active');
        $table->nullableTimestamps();
    });

[GrahamCampbell]

What happens here is that the DB::statement() part is executed separately. so what I did is wrapped by code in a transaction like so

Yeh, I don't think we support that. Ping @taylorotwell.

[malhayek2014]

@GrahamCampbell are you saying that DB::statement() is not supported with in a DB::beginTransaction(); block or DB::beginTransaction(); is not supported when using SQL Server?

Also, are you requesting me to send a message to @taylorotwell ?

[GrahamCampbell]

@GrahamCampbell are you saying that DB::statement() is not supported with in a

Yes.

Also, are you requesting me to send a message to @taylorotwell ?

No, me writing "Ping @taylorotwell." was me sending him a message. ;)

[marijnz0r]

Is there any way to solve the problem of not being able to insert ids in MSSQL through seeding yet?

[MikeAlhayek]

@marijnz0r, I had the same problem. As a workaround, I generated a T-SQL script and executed the whole thing using DB::statement()

[MikeAlhayek]

Here is an example

DB:statement('

BEGIN TRANSACTION;
SET IDENTITY_INSERT dbo.TBL1 ON;

INSERT INTO dbo.TBL1(Id,Name)VALUES(100,\'Test\');

SET IDENTITY_INSERT dbo.TBL1 OFF;
COMMIT;

');

[laurencei]

For anyone finding this thread via google etc - see this issue thread where it has been resolved: #27778