I came across an interesting problem last night – a stored procedure that had been created years ago was suddenly coming up with this error message:
UPDATE 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.
This error message means the settings for ANSI_NULLS and QUOTED_IDENTIFIER were different at the time the procedure was created compared to when the table was created. You will notice that whenever you script out an object from sql server management studio that it will add the SET options at the top of the script. In this case it appears the stored procedure was created without the correct SET options.
To fix the problem, you will need to recreate / alter the stored procedure with the correct SET options turned on before the CREATE PROCEDURE / ALTER PROCEDURE statement.
SET NUMERIC_ROUNDABORT OFF
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
alter procedure dbo.myprocedure
....(stored procedure body)
Seeing as you’ve already had this problem once – it is worthwhile checking your entire database to see if there are any other procedures creates with QUOTED_IDENTIFIER set off. Luckily Andrei Volkov has already provided the answer on how to do this:
SCHEMA_NAME(s.schema_id) + '.' + s.name AS name,
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s
s.type IN ('P','TR','V','IF','FN','TF')
AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 0
ORDER BY SCHEMA_NAME(s.schema_id) + '.' + s.name DESC
You should note that the SQL CAT team have recently posted that the incorrect QUOTED_IDENTIFIER setting can cause problems with persisted computed columns. The SQLCAT ISV team have also posted about issues with filtered indexes.
If you read the above articles you’ll now see why all scripts generated by sql server management studio include SET QUOTED_IDENTIFIER ON