Posted by Erland Sommarskog on 10/02/47 11:53

John (johnxhc@yahoo.com) writes:
> I have a very strange problem, it only happen to one SQL Server, other
> SQL Server seems to be fine
>
> I got the following error when trying to run a sp against one of the
> SQL Server:
>
> SELECT 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.
>
> If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,
> everything works fine, but the questions is why should I do that? and
> why it only happen to only one SQLServer ? The database option on
> QUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)

There are a couple of features in SQL Server that requires that the
setting QUOTED_IDENTIIER is ON. They are:

o Indexed views.
o Indexed computed columns.
o XQuery.

Important to understand is that the setting of QUOTED_IDENTIFIER is saved
with the procedure. The same applies to the ANSI_NULLS setting, whereas
for other SET options the run-time setting apply. (Save ANSI_PADDING where
it depends on the setting when the table column was created.)

You can determine the create-time setting for a stored procedure with
this SELECT:

SELECT uses_quoted_identifier, uses_ansi_nulls
FROM sys.sql_modules
WHERE object_id = object_id('yoursp')

As to why a procedure was created with QUOTED_IDENTIFIER off, the most
likely reason in SQL 2005 is that the procedure was loaded through
SQLCMD, which by default has QUOTED_IDENTIFIER off. (Always use the -I
option with SQLCMD to circumvent this problem.) Another possibility is
that the database origins from SQL 2000, where also Enterprise Manager
had QUOTED_IDENTIFIER (and ANSI_NULLS) off by default.

I suspect that the reason it appearst to work if you put SET
QUOTED_IDENTIFIER OFF in the procedure is simply because you reload
the procedure with the correct setting.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]