Question On QUOTED_IDENTIFIER

Jul 21, 2006

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 FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATA estdb.mdf' ,
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATA estdb_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

View 4 Replies


ADVERTISEMENT

SET QUOTED_IDENTIFIER OFF

Jul 20, 2005

Hello!How bad is it to use SET QUOTED_IDENTIFIER OFF before an INSERT andthen switch it ON straight afterwards. So I can insert strings likeO'Neil etc.It's my only design option at the moment. Everything works fine and asfar as I can see, it's only set to OFF for the currentconnection/session so no other users will run into problems at thesame time if they need it ON, right?Thanks Martin

View 4 Replies View Related

I Don't Understand Quoted_identifier

Mar 24, 2008

OK, so I know that following works if I want update/insert the value "joe's bar" into the table.


set SET QUOTED_IDENTIFIER on
update #temp set id=4 where name ='joe''s bar'


The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :)

View 4 Replies View Related

QUOTED_IDENTIFIER && ANSI_NULLS

Dec 2, 2007

does anyone know how to keep QA from adding the lines setting thesetwo options on and off along with blank lines at the beginning and endof every object you edit? i have searched quite a bit on this buthaven't been able to come up with anything.

View 1 Replies View Related

SET QUOTED_IDENTIFIER Question

Jul 13, 2007

When I execute a SET QUOTED_IDENTIFIER statement, does it only apply to the current batch or current connection? I noticed it doesn't change the "Quoted Identifiers Enabled" setting in the Options page of the Database Properties in Management Studio.



Thanks,



-Dave

View 5 Replies View Related

Find Which SPs Have Quoted_identifier Set Wrong

Jul 31, 2006

Our client's application software requires all stored procedures tohave quoted_identifier set a certain way. I've tripped over this afew times and promptly fixed it, but this morning, I had totroubleshoot a case where someone else tripped over it. In such asituation, how can I identify which SP(s) have it set the wrong way?(No huge rush, the someone-else has already fixed the recent case)SQL 2K, SP3

View 2 Replies View Related

XQuery Requires QUOTED_IDENTIFIER

Dec 13, 2005

I am working for a client that would has XML data being passed into a Service Broker queue.

View 8 Replies View Related

Set QUOTED_IDENTIFIER And ANSI_NULLS On All Objects...

Sep 6, 2006

Is there any way via T-SQL to run through a database and ensure that QUOTED_IDENTIFIER and ANSI_NULLS is set for all stored procs and functions etc on a database without having to recreate every proc / fn ?

The reason this is an issue is I'm trying to take advantage of indexed views to get a performance increase in an application - but not all of the procs/fn's have historically been created with ANSI_NULLS and QUOTED_IDENTIFIER set - so any proc that's not set correctly will fail once the indexed view has been created.

I could I suppose, script out all the procs / functions and do a bit of search and replace to ensure that these are set correctly by recreating all procs and functions - but I'm trying to avoid doing that as I've over 500 databases to upgrade - a metadata hack may be preferable.


Thoughts or comments or possible alternative approaches are welcome...

View 3 Replies View Related

Incorrect Settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.

Jul 20, 2005

Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."error after creating a view.We wanted a composite unique constraint that ignored nulls, so we setup a view using the following script:/* --- start --- */BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITGOCREATE VIEW vw_MyViewWITH SCHEMABINDINGASSELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULLGO/* --- end --- */and then added the constraint to the new view/* --- start --- */CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ONvw_MyView(Col1, Col2)GO/* --- end --- */I thought we were doing fine, 'til we started running some DELETEstored procedures and got the above error. The error also citedARITHABORT as an incorrect setting until we ran this script:/* --- start --- */USE masterDECLARE @value intSELECT @value = value FROM syscurconfigsWHERE config = 1534SET @value = @value | 64EXEC sp_configure 'user options', @valueRECONFIGURE/* --- end --- */TIA to anyone kind enough to shed some light on this for me. Is theresomething we should have done differently in creating the view andindex? If not, what's the procedure for working through thesesettings errors?I've read through some other threads on this subject, but didn'treally find what I was looking for. Thanks again for any help. Wouldbe appreciated.-matt

View 3 Replies View Related

DELETE Failed Because The Following SET Options Have Incorrect Settings: 'QUOTED_IDENTIFIER'

Sep 20, 2006

When I want to delete a data from a table that this tabl has a triggerand this trigger reached another tables to delete the data in cursor Ihave this messeage:DELETE failed because the following SET options have incorrectsettings: 'QUOTED_IDENTIFIER'.My trigger :CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS]FOR DELETEASBEGINDECLARE @rows_affected int, @inc bigint , @dblid bigint ,@DEPOBKINCbigintSELECT @rows_affected = @@ROWCOUNTIF @rows_affected = 0RETURN -- No rows changed, exit triggerBEGINDECLARE Miktar CURSOR FORSELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINCFROM deletedOPEN MiktarFETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINCWHILE @@fetch_status = 0BEGINSET QUOTED_IDENTIFIER ONDELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINCAND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1SET QUOTED_IDENTIFIER OFFPRINT @DEPOBKINCFETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINCENDCLOSE MiktarDEALLOCATE MiktarENDEND

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved