Question on QUOTED_IDENTIFIER

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)

Thanks in advance.
John

Enclose is the statement that create the database

if db_id('testdb') is not null
drop database [testdb]
go
begin
USE [master]

CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
SIZE = 29504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',

@.new_cmptlevel=90

ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [testdb] SET ANSI_NULLS OFF

ALTER DATABASE [testdb] SET ANSI_PADDING OFF

ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF

ALTER DATABASE [testdb] SET ARITHABORT OFF

ALTER DATABASE [testdb] SET AUTO_CLOSE OFF

ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE [testdb] SET AUTO_SHRINK OFF

ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [testdb] SET ENABLE_BROKER

ALTER DATABASE [testdb]
SET
AUTO_UPDATE_STATISTICS_ASYNC ON

ALTER DATABASE [testdb]
SET
DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [testdb] SET TRUSTWORTHY OFF

ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [testdb] SET READ_WRITE

ALTER DATABASE [testdb] SET RECOVERY FULL

ALTER DATABASE [testdb] SET MULTI_USER

ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [testdb] SET DB_CHAINING OFF

endAnother interesting thing, on the server that does not generate the
error, even I put SET QUOTED_IDENTIFIER ON inside the SP, the sp still
work without any error, so it looks on the particular sever the
QUOTED_IDENTIFIER has to be off OFF inside the SP, is this because of a
Server Side setting??

John wrote:

Quote:

Originally Posted by

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)
>
Thanks in advance.
John
>
Enclose is the statement that create the database
>
if db_id('testdb') is not null
drop database [testdb]
go
begin
USE [master]
>
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
SIZE = 29504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',
>
@.new_cmptlevel=90
>
ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF
>
ALTER DATABASE [testdb] SET ANSI_NULLS OFF
>
ALTER DATABASE [testdb] SET ANSI_PADDING OFF
>
ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF
>
ALTER DATABASE [testdb] SET ARITHABORT OFF
>
ALTER DATABASE [testdb] SET AUTO_CLOSE OFF
>
ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON
>
ALTER DATABASE [testdb] SET AUTO_SHRINK OFF
>
ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON
>
ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF
>
ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL
>
ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF
>
ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF
>
ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF
>
ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF
>
ALTER DATABASE [testdb] SET ENABLE_BROKER
>
ALTER DATABASE [testdb]
SET
AUTO_UPDATE_STATISTICS_ASYNC ON
>
ALTER DATABASE [testdb]
SET
DATE_CORRELATION_OPTIMIZATION OFF
>
ALTER DATABASE [testdb] SET TRUSTWORTHY OFF
>
ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON
>
ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE
>
ALTER DATABASE [testdb] SET READ_WRITE
>
ALTER DATABASE [testdb] SET RECOVERY FULL
>
ALTER DATABASE [testdb] SET MULTI_USER
>
ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM
>
ALTER DATABASE [testdb] SET DB_CHAINING OFF
>
end

|||I think that the state of QUOTED_IDENTIFIER that is used for a stored
procedure is the state that was in place WHEN the stored procedure was
created on the server. The QUOTED_IDENTIFIER state is saved with the stored
procedure metadata.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"John" <johnxhc@.yahoo.comwrote in message
news:1153494983.858599.96180@.m79g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Another interesting thing, on the server that does not generate the
error, even I put SET QUOTED_IDENTIFIER ON inside the SP, the sp still
work without any error, so it looks on the particular sever the
QUOTED_IDENTIFIER has to be off OFF inside the SP, is this because of a
Server Side setting??
>
John wrote:

Quote:

Originally Posted by

>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)
>>
>Thanks in advance.
>John
>>
>Enclose is the statement that create the database
>>
>if db_id('testdb') is not null
>drop database [testdb]
>go
>begin
>USE [master]
>>
>CREATE DATABASE [testdb] ON PRIMARY
>( NAME = N'testdb',
> FILENAME = N'C:\Program Files\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\testdb.mdf' ,
> SIZE = 8192KB ,
> MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
>LOG ON
>( NAME = N'testdb_log',
> FILENAME = N'C:\Program Files\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\testdb_log.ldf' ,
> SIZE = 29504KB ,
> MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
>COLLATE SQL_Latin1_General_CP1_CI_AS
>EXEC dbo.sp_dbcmptlevel @.dbname=N'testdb',
>>
>@.new_cmptlevel=90
>>
>ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFF
>>
>ALTER DATABASE [testdb] SET ANSI_NULLS OFF
>>
>ALTER DATABASE [testdb] SET ANSI_PADDING OFF
>>
>ALTER DATABASE [testdb] SET ANSI_WARNINGS OFF
>>
>ALTER DATABASE [testdb] SET ARITHABORT OFF
>>
>ALTER DATABASE [testdb] SET AUTO_CLOSE OFF
>>
>ALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ON
>>
>ALTER DATABASE [testdb] SET AUTO_SHRINK OFF
>>
>ALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ON
>>
>ALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFF
>>
>ALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBAL
>>
>ALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFF
>>
>ALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFF
>>
>ALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFF
>>
>ALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFF
>>
>ALTER DATABASE [testdb] SET ENABLE_BROKER
>>
>ALTER DATABASE [testdb]
> SET
>AUTO_UPDATE_STATISTICS_ASYNC ON
>>
>ALTER DATABASE [testdb]
> SET
>DATE_CORRELATION_OPTIMIZATION OFF
>>
>ALTER DATABASE [testdb] SET TRUSTWORTHY OFF
>>
>ALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ON
>>
>ALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLE
>>
>ALTER DATABASE [testdb] SET READ_WRITE
>>
>ALTER DATABASE [testdb] SET RECOVERY FULL
>>
>ALTER DATABASE [testdb] SET MULTI_USER
>>
>ALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUM
>>
>ALTER DATABASE [testdb] SET DB_CHAINING OFF
>>
>end

>

|||John (johnxhc@.yahoo.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Another improvement in your code is to use the ANSI/ISO double quote
marks instead of the dialect square brackets and single quotes.