Quoted Identifier nonsense

I Have a query that does the following:
SELECT PAT.LastName + ", " + PAT.FirstName AS FirstName FROM X
I have Quoted Identifiers turned OFF and yet this query fails because it
says that ',' is not a valid column name.
Um, unless everything has changed, the expression in " " should be treated
as a constant with quoted identifiers off, right?
Any other ideas?Are you sure you have the setting OFF? Try the following in QA:
SET QUOTED_IDENTIFIER OFF
GO
SELECT au_fname + ", " + au_lname
FROM Pubs..authors;
--
- Anith
( Please reply to newsgroups only )|||Ok,
I did that and then it worked UNTIL I closed my Query Analyzer window and
then we are back to the same behavior. I am SURE the setting is off in the
DB properties otherwise...
I am getting concerned
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OUIiK4GtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> Are you sure you have the setting OFF? Try the following in QA:
> SET QUOTED_IDENTIFIER OFF
> GO
> SELECT au_fname + ", " + au_lname
> FROM Pubs..authors;
> --
> - Anith
> ( Please reply to newsgroups only )
>|||It is simply a QA behaviour which you are seeing. QA sets SET
QUOTED_IDENTIFIER to ON by default and so, you have to explcitly set to OFF
to make your query work.
To behave the same way as in SQL 7.0, keep the setting to OFF. This has
little to do with the overall behaviour of the database.
--
- Anith
( Please reply to newsgroups only )|||Setting it as a database property doesn't work at all. Every time you start
a new QA session, ODBC sends its own SET commands to SQL Server which
override anything you have set at the db level. I wrote a column for SQL
Server Magazine pointing out that in most cases setting database options for
the ANSI behaviors is absolutely useless, because it always gets overridden
at the session level.
Even in YOU don't set it, the API sets it behind the scenes for you. You can
watch this happen in Profiler. :-)
For the Query Analyzer Tool, you can go to Tools|Options|Connection
Properties, and clear the box for setting Quoted Identifer. Then all new
connections in QA will have this OFF. But if you use other tools, they will
not be affected.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:u8qBu9GtDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Ok,
> I did that and then it worked UNTIL I closed my Query Analyzer window and
> then we are back to the same behavior. I am SURE the setting is off in
the
> DB properties otherwise...
> I am getting concerned
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OUIiK4GtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> > Are you sure you have the setting OFF? Try the following in QA:
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SELECT au_fname + ", " + au_lname
> > FROM Pubs..authors;
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||In addition to the other posts:
Is there any particular reason why you want to use double quotes for string delimiters? This is
non-standard and SQL Server is moving away from this (as you have noticed).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message news:u8qBu9GtDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Ok,
> I did that and then it worked UNTIL I closed my Query Analyzer window and
> then we are back to the same behavior. I am SURE the setting is off in the
> DB properties otherwise...
> I am getting concerned
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OUIiK4GtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> > Are you sure you have the setting OFF? Try the following in QA:
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SELECT au_fname + ", " + au_lname
> > FROM Pubs..authors;
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>