4

I have this error on executing stored procedure:

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.

Procedure is created with QUOTED_IDENTIFIER flag set to ON

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'sp_procedure') AND TYPE IN (N'P', N'PC'))
  BEGIN
    DROP PROCEDURE  [dbo].[sp_procedure]
  END
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_procedure]
(   
     @param_1 XML
    ,@param_2 INT
)
AS
BEGIN
   -- CODE
END

SELECT statement from sys.sql_modules shows that uses_quoted_identifiers somehow is set to 0.

I have already tried to execute following code. It run in one batch.

SET QUOTED_IDENTIFIER ON; 
EXEC sp_procedure @param_1 = N'<?xml version="1.0" encoding="utf-16"?>
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" />', @param_2= 51326

But it does not help.

Each session is created with QUOTED_IDENTIFIER set to 1:

set quoted_identifier on 
set arithabort off 
set numeric_roundabort off
set ansi_warnings on

Any ideas what can it be?

UPD Turned out that after running this specific script, there also run lots of other files. And one of them just recreated stored procedure with QUOTED_IDENTIFIER set to OFF.

Thank you for your help

makambi
  • 1,100
  • 3
  • 13
  • 30
  • 1
    Command(s) completed successfully. – Devart Jan 02 '14 at 14:20
  • Something about your repro is not right. It succeeds. Can you post a complete repro that I can run in tempdb? – usr Jan 02 '14 at 14:24
  • runs successfully here as well. – StackTrace Jan 02 '14 at 14:27
  • This is hopefully just a made up name, but you should avoid using an `sp_` prefix in the name of stored procedures. – Damien_The_Unbeliever Jan 02 '14 at 14:29
  • @usr, I wish I could Unfortunately there code is tight coupled to database which is pretty big. I aware that this simple example will succeed. I rather need a tip where else to look, like an SQL Server properties (QUOTED_IDENTIFIER is set to ON there by the way) or maybe I missed smth. Profiler shows there there is no statement that turns QUOTED IDENTIFIER off – makambi Jan 02 '14 at 14:30
  • I vaguely remember that indexes and indexed views can also be affected by the quoted-id setting. Look at the docs of the system catalogs for columns named with a reference to QUOTED_IDENTIFIER. Audit your entire database and migrate it to the recommended setting. The legacy SET options are a pest that must be exterminated from every system. (Alternative: script the db and look for the string QUOTED_IDENTIFIER). – usr Jan 02 '14 at 14:33
  • @usr, I will have a look. Thank you – makambi Jan 02 '14 at 14:43
  • Does this procedure call any other stored procedures? The quoted identifer setting to use when running each stored proc is stored on a per-proc basis and is based on what was in effect when the `CREATE`/`ALTER` was executed. The runtime value for your connection/session doesn't matter. – Damien_The_Unbeliever Jan 02 '14 at 15:51
  • @Damien_The_Unbeliever It does not call any. There are two delete statements that join incoming XML to views (do not ask me why views - not my code). Views have QUOTED_IDENTIFIER set to ON in sys.sql_modules – makambi Jan 02 '14 at 18:11

1 Answers1

0

Pleasure make sure the table and view definitions are also created with quoted identifier on.

Thomas Kejser
  • 1,264
  • 1
  • 10
  • 30