Laravel SQL Server Insert Error

Question

I am using Laravel's SQL Server driver for Eloquent and attempt to read and write to the database.

I am able to successfully read data from the database.

I am able to save to the database if I paste my query directly into my database client (Navicat).

However when I attempt to insert data via Eloquent I get an error.

//returns valid results
$results = DB::connection('next')->table('CMS_Tree')->get();

//throws exception
$sql = "INSERT INTO CMS_Tree (
            [NodeAliasPath], [NodeName], [NodeAlias], [NodeClassID], [NodeLevel], [NodeSiteID], [NodeGUID]
        ) VALUES (
            '/Home', 'Home', 'Home', 1095, 1, 1, '17B28018-C4DC-4896-ADAC-29F1E35B69BA'
        )";

$result = DB::connection('next')->statement($sql);

The exception message:

General error: 1934 General SQL Server error: Check messages from the SQL Server [1934] (severity 16) [(null)]

My research on that error suggests there could be an issue in how I am sending the data, but I'm unsure how to simplify this further.

Schema:

Can you provide your table structure? – Slava Murygin Aug 08 '16 at 19:07 — Slava Murygin, Aug 08 '16 at 19:07

score 1 · Accepted Answer · edited May 23 '17 at 12:08

I was able to solve this in 2 parts.

First, I needed to debug what "SET" parameters the client was sending in the background along with my sql to to the server. The hidden SET parameters are why the insert worked via the client, but not from my php code. This answer shows you how to determine which SET parameters you need to add:

SQL Server error 1934 occurs on INSERT to table with computed column PHP/PDO

Second, before I run an insert command, I manually need to pass these "SET" parameters. I need to run this block of code one time before one or multiple inserts. In my case, it looks like this:

$pdo = DB::connection('my-db-config-key')->getPdo();
$pdo->exec('SET ANSI_WARNINGS ON');
$pdo->exec('SET ANSI_PADDING ON');
$pdo->exec('SET ANSI_NULLS ON');
$pdo->exec('SET ARITHABORT ON');
$pdo->exec('SET QUOTED_IDENTIFIER ON');
$pdo->exec('SET ANSI_NULL_DFLT_ON ON');
$pdo->exec('SET CONCAT_NULL_YIELDS_NULL ON');
$pdo->exec('SET XACT_ABORT ON');

//run one or multiple inserts here

Laravel SQL Server Insert Error

1 Answers1