October 29, 2014 at 6:27 am
Good Day,
we are running an SQL Server 2008R2 database in our Production Area. In order to improve performance I added a 4 Filtered Indexes . However some of our jobs failed returning the follwoing errormessage :
"INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. 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"
The Indexes were created with the Default value Of " Quoted Idenfiier Enabled False".
I had a look at some of the Store dProcedures and they were created with either
"SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO "
OR
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO.
Could this mixed Standard have any bearing on the above mentioned error ?
Your feedback will be much appreciated.
Lianv
October 29, 2014 at 7:04 am
When you work with filtered index or indexed view, there are some set options setting that every session that works with the table that is the base for those indexes must have. The following set options must be used:
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
Don’t forget that with stored procedures the set options of ANSI_NULLS and QUOTED_IDENTIFIER is determined according to the settings that were used during the procedure's creation and not according to the session that used the procedure, so if you have a procedure that had the quoted_identifier set to off while it was created, this procedure won't be able to use to modify data that changes also the filtered index.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply