Get the settings associated with a stored procedure in relation to filtered indexes

Question

I quite like using filtered indexes in the proper context.

I have added the following filtered index:

USE [COLA];
CREATE UNIQUE NONCLUSTERED INDEX F_InterviewReport 
ON [dbo].[tbl_application_documents] (  [DocumentID] ASC )
include( State )
WHERE (DocumentClass = 'cadirect.onlineforms.InterviewReport')
WITH (  PAD_INDEX = OFF, FILLFACTOR = 90   , SORT_IN_TEMPDB = OFF , ONLINE = OFF, 
DROP_EXISTING = ON, 
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, 
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES] 

Everything worked fine on server_staging but on server_production I got the following error message:

Msg 1934, Level 16, State 1, Procedure usp_ins_document_details, Line 32 [Batch Start Line 2] 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.

Both servers are at the moment at this cumulative update:

Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64)
May 16 2019 02:24:21 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

The procedure in question is very trivial, it is posted here:

CREATE PROCEDURE [dbo].[usp_ins_document_details]  
/*   
 * author's name 
 * 27/07/2004  
 *  
 * Amended  
 * 28/10/2005  
 *  
 * Prevent the SP trying to insert the same record twice -- seems to  
 * be happening with regards to the InterviewReport. Quicker to sort  
 * this out that debug millions of lines of code.  
 *  
 * _sigh_ I am *such* a hack.  
 *  
 */  
(  
 @appid   numeric,  
 @doc   varchar(50),  
 @path   varchar(255)  
)  
AS   
 DECLARE @already int  
  
 SELECT  @already = COUNT(*)   
 FROM  tbl_application_documents  
 WHERE applicationid = @appid  
 AND documentclass = @doc  
  
 IF( @already = 0 )  
  BEGIN  
  
  INSERT INTO  tbl_application_documents  
    (ApplicationID,DocumentClass,UserFilePath,DateCreated)  
  VALUES  (@appid,  @doc, @path, getdate() )  
  SELECT @@IDENTITY AS DocumentID  
  
  END  
 ELSE  
  BEGIN  
    
  SELECT  DocumentID  
  FROM  tbl_application_documents  
  WHERE applicationid = @appid  
  AND documentclass = @doc  
  
  END  

Questions:

1. How can I find out the current settings of the procedure in each of those servers?
2. SET options have incorrect settings: ANSI_NULLS, QUOTED_IDENTIFIER - what are the correct settings?

Answer 1

The QUOTED_IDENTIFIER and ANSI_NULLS settitngs are "sticky"; the session settings used during proc creation are remembered and used at execution time, overriding the client session settings. CREATE/ALTER the proc after executing SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON. See my Improper SET Option Errors article. - Dan Guzman

---

The answers to both of your questions can be found in the article:

1. How can I find out the current settings of the procedure in each of those servers?

Dan provides the following code:

--stored procedures, views, functions, triggers with QUOTED_IDENTIFIER or ANSI_NULLS OFF
 SELECT
       OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName
     , OBJECT_NAME(o.object_id) AS ObjectName
     , o.type_desc AS ObjectType
 FROM sys.objects AS o
 WHERE
     0 IN(
           OBJECTPROPERTY(o.object_id, 'ExecIsQuotedIdentOn')
         , OBJECTPROPERTY(o.object_id, 'ExecIsAnsiNullsOn')
 )
 ORDER BY
       SchemaName
     , ObjectName;
 --columns with ANSI_PADDING OFF
 SELECT
       OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
     , OBJECT_NAME(t.object_id) AS ObjectName
     , c.name AS ColumnName
 FROM sys.tables AS t
 JOIN sys.columns AS c ON c.object_id = t.object_id
 JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
 WHERE
     c.is_ansi_padded = 0
     AND (
         (ty.name IN ('varbinary','varchar') AND c.max_length <> -1)
         OR (ty.name IN ('binary','char') AND c.is_nullable = 1)
 );

2. SET options have incorrect settings: ANSI_NULLS, QUOTED_IDENTIFIER - what are the correct settings?

Dan says:

QUOTED_IDENTIFIER, ANSI_NULLS, and ANSI_PADDING Session Settings

Given these session settings are initially set properly to ON by default, an OFF setting is a result of one or more of the following:

• An explict T-SQL SET OFF statement after the connection is made
• Non-default OFF setting specified by API connection (e.g. connection string keyword, DSN property, or set in app code programmatically)
• Overridden by a persisted object meta-data setting