Question on QUOTED_IDENTIFIER -- sql server (microsoft)

John
7/21/2006 7:43:17 AM

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
7/21/2006 8:16:23 AM

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??

[quoted text, click to view]

Arnie Rowland
7/21/2006 8:40:23 AM

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

[quoted text, click to view]

Erland Sommarskog
7/21/2006 3:54:07 PM

John (johnxhc@yahoo.com) writes:
[quoted text, click to view]

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (microsoft)

sql server (microsoft) : Question on QUOTED_IDENTIFIER