Jim Singelis
asked on
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.
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.
You can do this server wide either at installation or cia the connections tab under the server properties menu.
oops via
ASKER
None of the default connection options are checked on the Connections tab of the Server Properties
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.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
if you use double quotes, and get a double quote in the string, you have the same problem?
ASKER
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.
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.