|
|
Posted by Arnie Rowland on 10/02/23 11:53
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.com> wrote in message
news:1153494983.858599.96180@m79g2000cwm.googlegroups.com...
> 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
>
Navigation:
[Reply to this message]
|