|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Question on QUOTED_IDENTIFIERSQL 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 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: Show quote > 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. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "John" <john***@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 > John (john***@yahoo.com) writes:
Show quote > I have a very strange problem, it only happen to one SQL Server, other There are a couple of features in SQL Server that requires that the> 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) 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, esq***@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 Another improvement in your code is to use the ANSI/ISO double quote
marks instead of the dialect square brackets and single quotes.  |
|||||||||||||||||||||||
Other interesting topics