Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtered indexes) require Quoted Identifiers enabled. As you probably know whatever value of QUOTED_IDENTIFIER was set at the time of SP creation — this is what will be used during its execution. Two most common errors you’ll see if the value is set wrongly are:
UPDATE failed because the following SET options have incorrect settings: ‘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.
CREATE INDEX failed because the following SET options have incorrect settings: ‘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.
Here’s how to find all SPs with QUOTED_IDENTIFIER set to OFF:
SELECT
SCHEMA_NAME(s.schema_id) + '.' + s.name AS name,
s.create_date,
s.modify_date,
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s
WHERE
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
In SQL 2000 you could mass-fix all SPs in bulk by setting bits 29 and 30 in sysobjects.status column but since SQL 2005 this column is no longer used to save the properties. It was a hack anyway.
