CODEWITHSUNDEEP
phpsqlsql-serverstored-procedurespdo
jjwdesign
jjwdesign

Reputation: 3322

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

I'm trying to execute a stored procedure from with PHP (PHP-PDO Connection). The stored procedure tests/runs perfectly from SQL Server Management Studio (SQL console); no errors reported. When I try to run it from PHP, it does not execute properly. I managed to add a TRY/CATCH to the stored procedure and I received the following error information from the PHP exec. Does anyone understand this error?

Note, I am doing some Dynamic Pivoting of data from temporary tables #MY_TABLE. I'm also dynamically building a Merge query string to be executed after. It's a bit of a complex stored procedure.

EXEC MY_STORED_PROCEDURE '24566572-290E-4FE5-9AF6-8EEE4804F091'

Array
(
    [0] => EXEC MY_STORED_PROCEDURE '24566572-290E-4FE5-9AF6-8EEE4804F091'
    [1] => stdClass Object
        (
            [queryString] => EXEC MY_STORED_PROCEDURE '24566572-290E-4FE5-9AF6-8EEE4804F091'
        )

    [2] => stdClass Object
        (
            [ErrorNumber] => 1934
            [ErrorSeverity] => 16
            [ErrorState] => 1
            [ErrorProcedure] => MY_STORED_PROCEDURE 
            [ErrorLine] => 139
            [ErrorMessage] => SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/
        )

)

I've noticed similar questions such as : SQL Server error 1934 occurs on INSERT to table with computed column PHP/PDO

But, the following is at the top of my stored procedure. I'm not sure why PHP PDO would not use the SET statements in the stored procedure.

USE [MYDBTEST]
GO
/****** Object:  StoredProcedure [dbo].[MY_STORED_PROCEDURE]    Script Date: 06/25/2014 14:29:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
GO
SET ANSI_PADDING ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO

Upvotes: 2

Views: 4057

Answers (3)

Koushik Das
Koushik Das

Reputation: 1

Check IF (SELECT SESSIONPROPERTY('ARITHABORT')) = 0 and SET ARITHABORT ON; at the top of the procedure body. This will solve the 1934 error.

Upvotes: 0

Umberto
Umberto

Reputation: 2060

This worked for me:

SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;

Upvotes: 0

jjwdesign
jjwdesign

Reputation: 3322

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;

Upvotes: 2

Related Questions