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

Question

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

Your `SET` options in SSMS are likely different than what is reflected in the php environment. Here's documentation for `SET` options: http://technet.microsoft.com/en-us/library/ms190707(v=sql.105).aspx Here's another SO article that may help: http://stackoverflow.com/questions/8471519/sqlcmd-how-to-display-all-the-set-option-setting-values — Dave Mason, Jun 26 '14 at 13:18
@DMason - I thought if I had the SET options in the Stored Procedure, then they would be used from PHP PDO. Is that not the case? Is there some way to tell PHP PDO to use the SET options in the Stored Procedure? — jjwdesign, Jun 26 '14 at 13:22
From what info you've provided, I would tend to agree that the `SET` options within the SP should be sufficient. Is it possible the php code is connecting to the wrong server/database? I'm far from being a good source of info for php. Perhaps some others can assist... — Dave Mason, Jun 26 '14 at 14:29
@DMason, It seems that I wasn't adding the SET statements into the stored procedure (SP). When I went to "modify" the SP (via MS SSMS), I put the SETs before the ALTER PROCEDURE. Once I placed them below the AS or BEGIN, then it saved and worked as intended. — jjwdesign, Jun 26 '14 at 14:52

score 2 · Accepted Answer · answered Jun 27 '14 at 12:43

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;

score 0 · Answer 2 · answered Dec 10 '15 at 08:39

0

This worked for me:

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

answered Dec 10 '15 at 08:39

Umberto

2,011
1
23
27

score 0 · Answer 3 · edited Jul 25 '17 at 11:56

0

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

edited Jul 25 '17 at 11:56

Vikas Sachdeva

5,633
2
17
26

answered Jul 25 '17 at 11:48

Koushik Das

1
1

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

3 Answers3