Sql-server – How to get the settings associated with a stored procedure in relation to filtered indexes

configurationfiltered-indexsql serversql-server-2016stored-procedures

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?

Best Answer

I think those setting are in your SSMS session (not in the proc itself unless you add the "Set" command in there).

Check in the SSMS [tools] -> [options] then under [Query Execution] -> [ANSI].

You can compare the setting and make sure they match with the SSMS (that is probably running locally on your non-prod server)