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
- How can I find out the current settings of the procedure in each of those servers?
- SET options have incorrect settings: ANSI_NULLS, QUOTED_IDENTIFIER - what are the correct settings?