none
SELECT failed because the following SET options have incorrect settin

    Question

  • I am getting the following error message:

    [-E-19:42]        Message:  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 query notifications and/or xml data type methods.

    I am using the following set options before the sp is created:

    SET ARITHABORT ON

    SET CONCAT_NULL_YIELDS_NULL ON

    SET QUOTED_IDENTIFIER ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    SET NUMERIC_ROUNDABORT OFF

    I looked on the internet and most of solutions were related to indexed views or computed columns. But I am neither using any indexed views nor computed columns. Also the same sp is working fine in one environment but giving the above error in another SQL server. I am using SQL Server 2005 with SP1.

    Please help me in finding the cause & the resolution for this issue.

    Wednesday, August 23, 2006 8:23 AM

Answers

  • Thanks for your inputs. I finally got the solution. The SET options were declared before the CREATE stored proc. The SP was called from a batch job & the batch job always overrides any set options. Hence the SET options were moved after the BEGIN of the sp. It worked then....the error reported was no where close to the actual issue
    Monday, August 28, 2006 6:17 AM

All replies

  • You need to first identity the statement(s) or SP that causes this error. Are you using xquery methods in your SPs? Are you using query notifications? Are you using features that use query notifications underneath the covers? You can query sys.sql_modules to find SPs that were created without SET QUOTED_IDENTIFIER, ANSI_NULLS ON. These settings get saved with the SP. Also, if a particular batch contains multiple SET QUOTED_IDENTIFIER statements then only the last one takes effect. So I am afraid there is not that much to help with just the error message.
    Friday, August 25, 2006 7:31 PM
  • Its seems like you have recently created some index on a calculated field. As Jayachandran correctly said, find out the sp and then look for fix.

    I encountered this error when I was doing checkDB for my database. It worked after setting the two command

    SET ARITHABORT ON
    SET QUOTED_IDENTIFIER ON

    Regards

    Sachin

     

     

     

    Saturday, August 26, 2006 11:29 AM
  • Thanks for your inputs. I finally got the solution. The SET options were declared before the CREATE stored proc. The SP was called from a batch job & the batch job always overrides any set options. Hence the SET options were moved after the BEGIN of the sp. It worked then....the error reported was no where close to the actual issue
    Monday, August 28, 2006 6:17 AM
  • That is right I was using xquery in the sp and the sp would run correctly from the query analyzer .But from the Report viewer which i as using it was failing. I added these to options within the Begin and it worked. Thanks for the suggestion.
    Friday, February 09, 2007 2:35 PM
  • Friday, May 30, 2008 2:47 PM
  • I am having a similar problem.  I am accessing a view from a third party program, and I recently changed the view definition.  Previously I had a correllated subquery referencing an xml field:

     

    ,(SELECT gg.xml_info.value('address_state[1]', 'VarChar(50)')

    FROM dbo._group gg WHERE gg.group_id=o.group_id)

    AS ShipState

     

    which I replaced with a udf:

    ,[dbo].[udf_strStateFromShipment](so.Shipment_id) AS ShipState

     

    The udf takes a given shipment_id, finds all shipstates to which that applies, and returns a comma delimited string of those states.  The previous correllated subquery, in the context of the entire view, would result in multiple rows for the same shipment_id -- that is the reason I rewrote it. 

     

    Anyway, based on this thread, I re-created the views and udf's using the SET...... parameters as outlined above.  The view runs fine from within SMS, but will not run from the third party software.  It claims:

     

    SQL Server database error 0x80040E14: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

    I ran ALTER VIEW as follows:

     

     

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ARITHABORT ON

    GO

    SET CONCAT_NULL_YIELDS_NULL ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    SET ANSI_PADDING ON

    GO

    SET ANSI_WARNINGS ON

    GO

    SET NUMERIC_ROUNDABORT OFF

    GO

    ALTER VIEW [dbo].[vwPortalShipments]

    AS.............

     

    I did the same with all the udf's referenced in the view.  Any advice is welcome.  TIA  D. Lewis

     

     

    Monday, December 08, 2008 11:47 PM
  • check the compatability of master db , change it to 90 , if its sql server 2005 machine and its comptability level is 80 and proceed with your part
    • Proposed as answer by Tiya01 Wednesday, October 05, 2011 9:54 AM
    Wednesday, July 29, 2009 10:32 PM
  • I had the same problem when I tried to give permission.  Got solved after setting the compatibility level to 90.  Thanks to Rajganesh.

    Regards

    Ami

    Tuesday, August 10, 2010 11:46 AM