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.

Answer 1

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.

Answer 2

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

 

 

 

Answer 3

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

Answer 4

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.

Answer 5

Answer 6

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

 

 

Answer 7

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

Answer 8

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