none
Warning: mssql_execute() [function.mssql-execute]: message: DELETE failed because the following SET options have incorrect settings:

    Question

  • I have a SP which is executing fine from SSMS but not from my PHP application. I'm getting the below error. Please advise.

    Warning: mssql_execute() [function.mssql-execute]: message: DELETE 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/or query notifications and/or XML data type methods and/or spatial index operations.

    Tuesday, August 07, 2012 7:33 PM

Answers

  • As I said, for ANSI_PADDING, CONCAT_NULL_YIELDS_NULL and ANSI_WARNINGS it's the run-time settings that apply, so it does not help to add them to the script that creates the stored procedure. (And since you presumably create the procedure from SSMS they are on when you create the SP anyway.)

    What can help is to put the SET commands for these option inside the stored procedure, although I think it is the wrong solution. The problem is in the client, not the procedure.

    I don't know if the API you are using even have connection strings (DB-Library has not), so I don't know if you can add anything there. How does your current connection string look like? Else my idea was that you should submit a separate batch with the SET commands.

    Please bear in mind that I have no knowledge about PHP as such.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 08, 2012 7:43 AM

All replies

  • Any idea how to resolve this issue please ?
    Tuesday, August 07, 2012 9:17 PM
  • When you use an indexed view, certain options must be set to the correct value.  Some connection methods by default set those options to the correct values values and some don't.

    I don't know PHP so I'm not sure how PHP connects to the database.  But it is apparently using a method that sets those values to the wrong setting.  A possible fix is immediately after making the connection, issue a series of set statements setting all the options to the correct value for indexed views.  You can find a list of the options that require the correct value and what the correct values are at

    http://msdn.microsoft.com/en-us/library/ms191432.aspx

    The above is probably the ideal method if PHP and/or your program don't depend on those SET options being set to values that don't work for indexed views.  If it doesn't work to SET those options immediately after you make the connection to SQL Server, you can put the required SET statements as the first statements in your stored procedure.  That will set these options only for the duration of the stored procedure, as soon as the stored procedure exits, the options will be returned to the original values. 

    Tom

    Tuesday, August 07, 2012 9:36 PM
  • There are several features in SQL Server that requires these setting to be ON: QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL. Example on such features are:

    * Indexed views.
    * Indexed computed columns.
    * Filtered indexes.
    * Xquery and xml type methods.
    * Query notification.

    I would assume that you are trying to execute a stored procedure, because the first two settings in the list above are saved with the procedure. The other three settings are set at run-time.

    All modern clients have these setting on by default. I've seen people having this problem with PHP. I suspect this is because they use an interface based on DB-Library which is a very old API, and which does not set these options when connection.

    One option is to issue these command when you connect:

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

    Another option is to rip out the API in favour of Microsoft's own PHP driver which you find on http://sqlsrvphp.codeplex.com/
    This is the solution I would recommend in the long term, since this gives you access to more data types and functionality in SQL Server than the old PHP API.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, August 07, 2012 10:07 PM
  • I would assume that you are trying to execute a stored procedure, because the first two settings in the list above are saved with the procedure. The other three settings are set at run-time.

                 - Yes, exactly. But when I got this error I saved all the following in the begining of the SP itself but still got the same error.

    SET

    ANSI_NULLS ON

    GO

    SET

    ANSI_PADDING ON

    GO

    SET

    ANSI_WARNINGS ON

    GO

    SET

    ARITHABORT ON

    GO

    SET

    CONCAT_NULL_YIELDS_NULL ON

    GO

    SET

    QUOTED_IDENTIFIER ON

    GO

    SET

    NUMERIC_ROUNDABORT OFF

    GO

                One option is to issue these command when you connect:

                                                -       You mean in the connection string of the php code?

    Tuesday, August 07, 2012 11:32 PM
  • As I said, for ANSI_PADDING, CONCAT_NULL_YIELDS_NULL and ANSI_WARNINGS it's the run-time settings that apply, so it does not help to add them to the script that creates the stored procedure. (And since you presumably create the procedure from SSMS they are on when you create the SP anyway.)

    What can help is to put the SET commands for these option inside the stored procedure, although I think it is the wrong solution. The problem is in the client, not the procedure.

    I don't know if the API you are using even have connection strings (DB-Library has not), so I don't know if you can add anything there. How does your current connection string look like? Else my idea was that you should submit a separate batch with the SET commands.

    Please bear in mind that I have no knowledge about PHP as such.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 08, 2012 7:43 AM