I have created a filtered non-clustered index in order to optimize particular set of queries, but I started to get the following errors from various sources:
UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. 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.
It seems that some of the legacy routines are created with the SET ANSI_NULLS OFF option and when the engine is trying to update the destination table from given context the error is thrown.
I am wondering is there a way to see which of the routines are created with this option. For example, if you script such routine you get something like this:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ....
I usually use scripts like the below one to found things in the objects definitions, but this settings is not part of it:
DECLARE @SearchWord NVARCHAR(128) = 'SET ANSI_NULLS OFF'
SELECT [ROUTINE_NAME]
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_DEFINITION] LIKE '%' + @SearchWord+'%'
UNION
SELECT OBJECT_NAME([id])
FROM [SYSCOMMENTS]
WHERE [text] LIKE '%' + @SearchWord + '%'
GROUP BY OBJECT_NAME([id])
UNION
SELECT OBJECT_NAME(object_id)
FROM [sys].[sql_modules]
WHERE [definition] LIKE '%' + @SearchWord + '%' ;
You could just install SQL Search from Redgate or similar products from other vendors. Then it's easy to search for things like this.
For a one-off, something like
SELECT * FROM sys.sql_modules WHERE definition LIKE '% ansi %'
should do it. or
SELECT * FROM sys.sql_modules WHERE definition uses_ansi_nulls = 1