QUOTED IDENTIFIER Errors in EEE

From EnablerWiki

Jump to: navigation, search

Created: 16 October 2009. Last Reviewed: 16 October 2009


Microsoft SQL Server has a number of settings that can impact on the operation of some types of queries. One of these is the setting of QUOTED_IDENTIFIER (on/off). SQL Server requires that QUOTED_IDENTIFIER be ON in certain circumstances. One circumstance which can affect EEE is when inserting or updating rows in a table that has an index on a computed column. TenderDetail is such a table in EEE. Further, if a stored procedure inserts or updates such rows then QUOTED_IDENTIFIER must be on when the stored procedure is compiled!.

Contents

Symtoms:

If this requirement is not met, SQL Server may return an error message such as “READTEXT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Veryify the SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type mthods.”

Cause:

In EEE this can occur if EEE stored procedures are recompiled while QUOTED_IDENTIFIER is off. Depending on how stored procedures are compiled, QUOTED_IDENTIFIER may be on or off. Stored Procedures installed using Magenta's standard EEE tools should be properly compiled. If stored procedures are applied manually, they may have been compiled with incorrect settings. There are various possible reasons:

  • SQLCMD, by default, has QUOTED_IDENTIFIER set to OFF
  • SQL Server Management Studio usually defaults QUOTED_IDENTIFER on, but this can be overridden in SSMS options
  • OLEDB connections normally default QUOTED_IDENTIFIER to ON

As SQLCMD is used by the EEE installers, upgrade tool and service release tool, any special (non-standard) scripts that fail to include SET QUOTED_IDENTIER ON at the start could also lead to this problem.


To Check Connection Settings for any Current Connection:

The following TSQL may be executed on any connection to display the current values for a variety of SET options.

IF 1 & @@options = 1PRINT 'DISABLE_DEF_CNST_CHK'IF 2 & @@options = 2PRINT 'IMPLICIT_TRANSACTIONS'

IF 4 & @@options = 4PRINT 'CURSOR_CLOSE_ON_COMMIT'

IF 8 & @@options = 8PRINT 'ANSI_WARNINGS'

IF 16 & @@options = 16PRINT 'ANSI_PADDING'

IF 32 & @@options = 32PRINT 'ANSI_NULLS'

IF 64 & @@options = 64PRINT 'ARITHABORT'

IF 128 & @@options = 128PRINT 'ARITHIGNORE'

IF 256 & @@options = 256PRINT 'QUOTED_IDENTIFIER'

IF 512 & @@options = 512PRINT 'NOCOUNT'

IF 1024 & @@options = 1024PRINT 'ANSI_NULL_DFLT_ON'

IF 2048 & @@options = 2048PRINT 'ANSI_NULL_DFLT_OFF'

IF 4096 & @@options = 4096PRINT 'CONCAT_NULL_YIELDS_NULL'

IF 8192 & @@options = 8192PRINT 'NUMERIC_ROUNDABORT'

IF 16384 & @@options = 16384PRINT 'XACT_ABORT'


Resolution:

These problems in EEE can be resolved by determining the problem stored procedure(s) and recompiling them with the appropriate SET QUOTED_IDENTIFIER ON.


For example if usp_SampleProc is the problem:

  • sp_helptext usp_SampleProc
  • Paste the stored procedure text into a query window
  • Delete the text up to, and including CREATE PROCEDURE and replace with ALTER PROCEDURE
  • Prior to ALTER PROCEDURE, insert:

SET QUOTED_IDENTIFIER ON

GO

Run the query.

See Also:

Retrieved from "http://theoracle.magentaretail.com.au/QUOTED_IDENTIFIER_Errors_in_EEE"
Personal tools