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:
- 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?
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)