Bevan at 03:10, 19 October 2009

2009-10-19T03:10:08Z

← Older revision		Revision as of 03:10, 19 October 2009
Line 4:		Line 4:
	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!. ''		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!. ''

	~~====~~= Symtoms: ~~====~~=		= 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.”		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: ~~====~~=		= 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:		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:

Line 49:		Line 49:
	IF 16384 & @@options <nowiki>=</nowiki> 16384PRINT 'XACT_ABORT'		IF 16384 & @@options <nowiki>=</nowiki> 16384PRINT 'XACT_ABORT'

	#

	= Resolution: =		= Resolution: =

Bevan: New page: 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 sett...

2009-10-19T03:09:00Z

New page: 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 sett...

New page

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!. ''

===== 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 <nowiki>=</nowiki> 1PRINT 'DISABLE_DEF_CNST_CHK'IF 2 & @@options <nowiki>=</nowiki> 2PRINT 'IMPLICIT_TRANSACTIONS'

IF 4 & @@options <nowiki>=</nowiki> 4PRINT 'CURSOR_CLOSE_ON_COMMIT'

IF 8 & @@options <nowiki>=</nowiki> 8PRINT 'ANSI_WARNINGS'

IF 16 & @@options <nowiki>=</nowiki> 16PRINT 'ANSI_PADDING'

IF 32 & @@options <nowiki>=</nowiki> 32PRINT 'ANSI_NULLS'

IF 64 & @@options <nowiki>=</nowiki> 64PRINT 'ARITHABORT'

IF 128 & @@options <nowiki>=</nowiki> 128PRINT 'ARITHIGNORE'

IF 256 & @@options <nowiki>=</nowiki> 256PRINT 'QUOTED_IDENTIFIER'

IF 512 & @@options <nowiki>=</nowiki> 512PRINT 'NOCOUNT'

IF 1024 & @@options <nowiki>=</nowiki> 1024PRINT 'ANSI_NULL_DFLT_ON'

IF 2048 & @@options <nowiki>=</nowiki> 2048PRINT 'ANSI_NULL_DFLT_OFF'

IF 4096 & @@options <nowiki>=</nowiki> 4096PRINT 'CONCAT_NULL_YIELDS_NULL'

IF 8192 & @@options <nowiki>=</nowiki> 8192PRINT 'NUMERIC_ROUNDABORT'

IF 16384 & @@options <nowiki>=</nowiki> 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: =

QUOTED IDENTIFIER Errors in EEE - Revision history

Bevan at 03:10, 19 October 2009

Bevan: New page: 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 sett...