Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to set Quoted_Identifier OFF server Wide

Posted on 2007-08-02
8
Medium Priority
?
833 Views
Last Modified: 2008-02-26
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.

0
Comment
Question by:studioEtc
  • 3
  • 3
  • 2
8 Comments
 
LVL 5

Expert Comment

by:gigglick
ID: 19620061
You can do this server wide either at installation or cia the connections tab under the server properties menu.
0
 
LVL 5

Expert Comment

by:gigglick
ID: 19620067
oops via
0
 

Author Comment

by:studioEtc
ID: 19620614
None of the default connection options are checked on the Connections tab of the Server Properties
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 5

Expert Comment

by:gigglick
ID: 19620628
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19620740
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
 

Author Comment

by:studioEtc
ID: 19621054
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19621064
if you use double quotes, and get a double quote in the string, you have the same problem?
0
 

Author Comment

by:studioEtc
ID: 19622047
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question