0

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?
1
  • 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 Mar 6 '20 at 23:01
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.