SQL Server Error 1934 Occurs on Insert to Table with Computed Column PHP/Pdo

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

Turns out the problem had to do with the SET parameters. I used the code below obtained from Here. To determine which options were set in SQL Server Management Studio (where the insert worked). Then placing each of those in an exec prior to my insert statement caused things to work again. I didn't need to keep all the options, so below I show the ones which were required to get it working.

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Here are the options I ended up needing:

$dbPDO->exec("SET ANSI_WARNINGS ON");                                                                               
$dbPDO->exec("SET ANSI_PADDING ON");
$dbPDO->exec("SET ANSI_NULLS ON");
$dbPDO->exec("SET QUOTED_IDENTIFIER ON");
$dbPDO->exec("SET CONCAT_NULL_YIELDS_NULL ON");

Update:
It seems FK constraints resulted in the following error. The above fixed this error as well.

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

How do I correct a Stored Procedure Error #1934? (SQL Server / PHP)

Ok, I figured the issue out with the SET statements. I was using MS SQL Server Management Studio (SSMS) to "Modify" the stored procedure. When I did this, I noticed two SET statements at the top of the page. Unfortunately, these SET statements were not within the ALTER PROCEDURE statement. So, when I tried adding additional SET statements to the top of the page, then were not part of the ALTER PROCEDURE statement and therefor not saved (altered). Since the page was not re-loaded/re-modified in any way, they remained at the top. So, the solution is to make sure you place any changes within the ALTER PROCEDURE.

USE [MYDBTEST]
GO
/****** Object:  StoredProcedure [dbo].[MY_STORED_PROCEDURE] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Jeff Walters
-- Create date: 6/20/2014
-- Description: 
-- 
-- =============================================

ALTER PROCEDURE [dbo].[MY_STORED_PROCEDURE]
                (
                @id CHAR(36) = NULL
                )
AS

BEGIN

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_WARNINGS ON;
    SET ANSI_PADDING ON;
    SET CONCAT_NULL_YIELDS_NULL ON;

Can't insert values into MSSQL with PDO

Explanation

This error occurs at run time when an attempt is made to use a null value while inserting or updating a column that does not allow null values.

Action

If this error occurs when you are running an UPDATE or INSERT statement, verify that the data inserted or updated matches the column definition for the affected table.

Inserting or updating a column does not allow null values.

Check this: http://www.lcard.ru/~nail/sybase/error/3742.htm or this: http://technet.microsoft.com/en-us/library/aa258724(v=sql.100).aspx

Laravel SQL Server Insert Error

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

How to implement LIMIT with SQL Server?

Starting SQL SERVER 2005, you can do this...

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

or something like this for 2000 and below versions...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

---

Related Topics