41

We are getting the below message sometimes while executing the stored procedure, after that without any change deleting and re-executing the stored procedure it is working fine.

DBCORE INSERT failed because the following SET options have incorrect settings: '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.

Kindly help us to understand this.

Improve this question

6 Answers 6

Reset to default
41

QUOTED_IDENTIFIER is a "sticky" option so the setting in effect when the procedure was created is used at runtime. Since no procedure changes were made, the error suggests the stored procedure was created with QUOTED_IDENTIFIER OFF and an index with one of the types mentioned in the error message was created/dropped.

Recreate or alter the stored procedure from a session with both QUOTED_IDENTIFIER ON and ANSI_NULLS ON to avoid the problem going forward.

EDIT:

Per the answer by @Leonidius and related comments, the SQLCMD command-line utility defaults to QUOTED_IDENTIFIER OFF for backwards compatibility. This behavior differs from SSMS and is a common gotcha when deployment scripts don't explicitly include QUOTED_IDENTIFIER ON statements before creating stored procedures and other objects.

I strongly suggest one make a habit of specifying the -I SQLCMD argument to ensure QUOTED_IDENTIFIER OFF is not set inadvertently.

Improve this answer
0
28

In my case I had to add -I parameter to sqlcmd.exe command line when loading stored procedures in the bulk mode.

Improve this answer
3
  • 10
    SQLCMD defaults to QUOTED_IDENTIFIER OFF for backwards compatibility so the -I option is needed when the executed scripts don't include QUOTED_IDENTIFIER ON before creating stored procedures.
    – Dan Guzman
    Commented Jun 24, 2019 at 14:36
  • 3
    My bcp version didn't have -I, but adding -q did the trick
    – Brad
    Commented Aug 12, 2019 at 20:35
  • Oh my god thanks, I was looking for this for days
    – Stav Shamir
    Commented Feb 11, 2021 at 8:32
16

I was running into this problem so I created all my Job's Steps into Stored procedures in MS SQL. When using the template it automatically has the following

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
Improve this answer
8

If anyone hits this error after having deployed via a Visual Studio database project then it's worth checking the properties of your stored procedure file. If this is set to Project Default then go to the properties of your database project > Project Settings and click the Database Setting button.

You can turn these two values (and others) to be on by default as shown in the screenshot below.

enter image description here

This will only fix the issue if you have changes to the procedure however; a schema compare and subsequent update will not change the QUOTED_IDENTIFIER in the stored procedure unless there are changes to the body of the procedure. It will prevent the problem from reoccurring on a subsequent schema updated though.

Improve this answer
1
  • 1
    Alternatively change the settings for your stored proc like this in the .sqlproj file: <Build Include="StoredProcedures\MyStoredProc.sql"><QuotedIdentifier>On</QuotedIdentifier><AnsiNulls>On</AnsiNulls></Build>
    – eddex
    Commented Jun 17, 2022 at 11:53
0

The issue I got had the same message but the issue was related to the position of the BEGIN TRANSACTION declaration which it was misplaced. enter image description here

Improve this answer
0

I was having the same issue in an SQL Job Step because I was trying to set a column named Status. So at the very top I added GO SET QUOTED_IDENTIFIER ON GO and in my SET statement I added double quotes to my column Status

UPDATE QES.dbo.WDQ894_IdsToProcess SET "Status" = 3
Improve this answer

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