QUOTED IDENTIFIER setting for XML VALUE() method

• Suresh.Utham

  SSCommitted

  Points: 1524

  More actions

  September 14, 2009 at 12:21 am

  #135637

  Hi All,

  I'm using a XML Value() method to parse a XML data & insert the data into a table. But i'm getting error as follows:

  ERROR:

  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 query notifications

  and/or xml data type methods.

  Here's the code snippet for your reference (part of an Stored Proc in SIT box):

  .

  .

  .

  SET QUOTED_IDENTIFIER OFF

  INSERT INTO Employee(EmpNo, EName)

  SELECT XMLColumn.value('(row/@EmpId)[1]', 'INT') AS EmpNo,

  XMLColumn.value('(row/@EmpName)[1]', 'CHAR(60)') AS EmpName

  FROM XMLTable

  SET QUOTED_IDENTIFIER ON

  .

  .

  .

  Does QUOTED IDENTIFIER get affected inside the SP... Or do i need to change the setting in my DB?

  Note: I'm not getting any error in my DEV box which has Quoted Identifier Enabled = False... Even in my SIT box, it's set to False.

  Any help in this issue is highly appreciated.

  Thanks in advance 🙂

  Suresh

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  September 14, 2009 at 8:42 am

  #1053250

  The QUOTED IDENTIFIER set option is set during the creation of the stored procedure and sticks with it, so modifying this set option at the session that activates the procedure, has no influence in the procedure. You can query the view sys.sql_modules in order to find out if your procedure was creates with set quated idenfifier on or off.

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/