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?
0

1 Answer 1

2

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)
 );
  1. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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