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?
Improve this question
0

1 Answer 1

Reset to default
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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.