Posted by John on 10/02/83 11:53

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

[Back to original message]