How to set Quoted_Identifier OFF server Wide

My database has quoted Identifier Enabled set to FALSE. So why does
select * from mytable where LastName =  "D'Amico"  fail?
If I explicitly set quoted_Identifier OFF before executing the query. It works.
Why must I explicitly set quoted_identifier OFF to make the above query work, is there a way to permanently make  LastName =  "D'Amico"  server wide.

studioEtcAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
QUOTED_IDENTIFIER = ON means that double quotes (") specify table and columns names (ie identifiers).
with
QUOTED_IDENTIFIED = OFF, double quotes can be used as string delimiter.

you should NOT use double quotes for string delimiters, but single quotes. this will though require you to duplicate the single quotes in the values, unless of course you using parametrized queries:
select * from mytable where LastName =  'D''Amico'
0
 
gigglickCommented:
You can do this server wide either at installation or cia the connections tab under the server properties menu.
0
 
gigglickCommented:
oops via
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
studioEtcAuthor Commented:
None of the default connection options are checked on the Connections tab of the Server Properties
0
 
gigglickCommented:
The default is set to true for this option 2000+ it's false in 7.  If you want it false try checking the option for quoted identifiers and re-run your query.  You may also need to restart the service.
0
 
studioEtcAuthor Commented:
Why should I not use double quotes for string delimiter?  Otherwise I have to use instr or charIndex to find and duplicate the apostrophe.

I'm working in vba on an Access front end for SQL Server 2005.  I have something like:

qstr = " select * from myTable where LastName = '" & currentName & "'  (thats ' " & currentName & " ' ")

Then using ado I make a connection and execute the query with  command.execute qstr.

If quoted_identifier were off then   where LastName = " & char(34) & currentName & char(34) & ";"  would work.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you use double quotes, and get a double quote in the string, you have the same problem?
0
 
studioEtcAuthor Commented:
I understand the a double quote could present the same problem as the single. In our case however, we get data in comma delimited files with double quotes as delimiters.

 Sill your statement seemed so adamant "you should NOT use double quotes".  It made me think I could get into trouble elsewhere by setting the quoted_identifier OFF.

In any case I didn't  know about using two single quotes together. At the moment writing a little function to turn single quote in to two single quotes seems the quickest solution to my problem.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.