Quotes within a query

Help, please.

I'm going crazy trying to figure out how to form this SQL query. I am querying an Informix linked server and I need to pass a variable date. I am using an expression to create the query like so

"Select count(*) from " + @.[User::varDBName] + ":informix.doc_tl WHERE " + @.[User::varDBName] +":informix.doc_tl.d_received = {D " + @.[User::varDate] +"} "

The informix query needs the date to be {D "2007-01-15"} but for the life of me, I can't get the date enclosed in quotes.

The error I get is

An OLE DB record is available. Source: "(null)". HResult: 0x80040E14

Description: "(null)"

Can anyone tell me what I'm doing wrong?

Thanks

Hi Chris,

Can you try using single quote character (') or escaped quote character i.e. using quote twice ("")?

-- Waseem

|||

Hi!

Thanks for the reply. I thought I tried both but upon retry, the single quote character worked. I do get a code page warning but I think that is OK.

Thanks so much!

Quotes or no quotes for calculated members

Guys,

I am a little confused by the calculated members that shows the MDX between quotes for converted cubes.

If I take off the quotes, it looks like it works so what are the quotes for?

I am talking about the form view of calculated members.

Thanks,

Philippe

In many cases, SQL Server 2000 requires quotes but SQL Server 2005 does not.

Quotes In BCP

I'm exporting via BCP. I'd like to have quotes around the text values. In
DTS, you have the text qualifier option. Is there a BCP option that
corresponds to the DTS option known as the Text Qualifier?
"SR" <mv2k_2003-news@.yahoo.com> wrote in message
news:7L9mc.5460$1T4.3283@.newssvr27.news.prodigy.co m...
> I'm exporting via BCP. I'd like to have quotes around the text values. In
> DTS, you have the text qualifier option. Is there a BCP option that
> corresponds to the DTS option known as the Text Qualifier?
>
In checking the BCP options, I could not find one that corresponds to DTS...
Steve
|||I cannot find a BCP way to do what you ask. You cannot make a " be the
field terminator. What you can do is still create a DTS package and run it
from the DTSRUN utility if you needed it to be command line.
Jeff Duncan
MCDBA, MCSE+I
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:eSZSddtMEHA.2468@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "SR" <mv2k_2003-news@.yahoo.com> wrote in message
> news:7L9mc.5460$1T4.3283@.newssvr27.news.prodigy.co m...
In
> In checking the BCP options, I could not find one that corresponds to
DTS...
> Steve
>
|||Yeah I knew about the DTS option. Thanks for the help everybody.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:%23hpg0CuMEHA.936@.TK2MSFTNGP11.phx.gbl...
> I cannot find a BCP way to do what you ask. You cannot make a " be the
> field terminator. What you can do is still create a DTS package and run
it
> from the DTSRUN utility if you needed it to be command line.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:eSZSddtMEHA.2468@.TK2MSFTNGP11.phx.gbl...
> In
> DTS...
>
|||SR,

> I'm exporting via BCP. I'd like to have quotes around the text
> values. In DTS, you have the text qualifier option. Is there a
> BCP option that corresponds to the DTS option known as the Text
> Qualifier?
You need to use a format file for this. Using the pubs..authors
table as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote
that just returns NULL. It's just a little trick to get the leading
quote on the first column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out authors_csv.dat -fauthors_csv.bcp -S. -T
Linda

Quotes and ImageURL/External behavour

Hi All,
Can anyone explain this weirdness? If I have an Image set to External
with a URL of http://localhost/myimagepage.asp?MediaID=82 I can see the
image in the designer and also at run time. This is without an equals
sign at the front as well, so just the plain string starting with http.
But...
If I then add an equals sign to the start of the string and put quotes
around it so that it reads;
="http://localhost/myimagepage.asp?MediaID=82"
Then broken images abound (red crosses), in the report design and at
run time.
My thoughts are that;
a) I have a broken version of reporting services (I have checked the
version and I have RS SP2 installed).
b) The report server is taking the string data as the image data, not
the URL of the image data.
Am I missing something obvious, it seems as if everyone else has this
figured.
Thanks in advanceWell, I found this one, a small flaw in Reporting Services and a
problem with Internet Explorer/Firefox.
There were two problems;
1) IE sometimes uploads files as mime type image/pjpeg, RS does not
like these.
2) The response object writing the HTTP headers from ASP.net required
the following code;
Response.Charset = ""
To fix point 1, I just checked for pjpeg and jpg and returned jpeg
instead, all works well.
The problem with RS is that it will let you stare at these things for
ages and not give you any detail on why it won't render. Additional
feedback (possibly via error images) would be great in the next
version.
Ryansql

Quotes

What is the difference using the single or double quotes in a stored
procedure ?
Ex1: where username = @.username
AND status = "D" -- or 'D'
Ex2: if @.stringtype = "D" -- or 'D'
Select ...............
Thanks.
See "SET QUOTED_IDENTIFIER" in BOL.
Example:
use northwind
go
set quoted_identifier off
go
select 1
where
"1111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111111111111111 11111111111111111111111111111" != '1'
go
set quoted_identifier on
go
select 1
where
"1111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111111111111111 11111111111111111111111111111" != '1'
go
The first "select" statement will return '1', but the second one will give
an error because sql server will consider the string as an identifier.
AMB
"DXC" wrote:

> What is the difference using the single or double quotes in a stored
> procedure ?
> Ex1: where username = @.username
> AND status = "D" -- or 'D'
> Ex2: if @.stringtype = "D" -- or 'D'
> Select ...............
>
> Thanks.
|||Thanks...........
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "SET QUOTED_IDENTIFIER" in BOL.
> Example:
> use northwind
> go
> set quoted_identifier off
> go
> select 1
> where
> "1111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111111111111111 11111111111111111111111111111" != '1'
> go
> set quoted_identifier on
> go
> select 1
> where
> "1111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111111111111111 11111111111111111111111111111" != '1'
> go
>
> The first "select" statement will return '1', but the second one will give
> an error because sql server will consider the string as an identifier.
>
> AMB
> "DXC" wrote:

Quotes

What is the difference using the single or double quotes in a stored
procedure ?
Ex1: where username = @.username
AND status = "D" -- or 'D'
Ex2: if @.stringtype = "D" -- or 'D'
Select ...............
Thanks.See "SET QUOTED_IDENTIFIER" in BOL.
Example:
use northwind
go
set quoted_identifier off
go
select 1
where
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111" != '1'
go
set quoted_identifier on
go
select 1
where
"11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111" != '1'
go
The first "select" statement will return '1', but the second one will give
an error because sql server will consider the string as an identifier.
AMB
"DXC" wrote:
> What is the difference using the single or double quotes in a stored
> procedure ?
> Ex1: where username = @.username
> AND status = "D" -- or 'D'
> Ex2: if @.stringtype = "D" -- or 'D'
> Select ...............
>
> Thanks.|||Thanks...........
"Alejandro Mesa" wrote:
> See "SET QUOTED_IDENTIFIER" in BOL.
> Example:
> use northwind
> go
> set quoted_identifier off
> go
> select 1
> where
> "11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111" != '1'
> go
> set quoted_identifier on
> go
> select 1
> where
> "11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111" != '1'
> go
>
> The first "select" statement will return '1', but the second one will give
> an error because sql server will consider the string as an identifier.
>
> AMB
> "DXC" wrote:
> > What is the difference using the single or double quotes in a stored
> > procedure ?
> >
> > Ex1: where username = @.username
> > AND status = "D" -- or 'D'
> >
> > Ex2: if @.stringtype = "D" -- or 'D'
> > Select ...............
> >
> >
> > Thanks.

Quotes

What is the difference using the single or double quotes in a stored
procedure ?
Ex1: where username = @.username
AND status = "D" -- or 'D'
Ex2: if @.stringtype = "D" -- or 'D'
Select ...............
Thanks.See "SET QUOTED_IDENTIFIER" in BOL.
Example:
use northwind
go
set quoted_identifier off
go
select 1
where
" 1111111111111111111111111111111111111111
11111111111111111111111111111111111
1111111111111111111111111111111111111111
1111111111111" != '1'
go
set quoted_identifier on
go
select 1
where
" 1111111111111111111111111111111111111111
11111111111111111111111111111111111
1111111111111111111111111111111111111111
1111111111111" != '1'
go
The first "select" statement will return '1', but the second one will give
an error because sql server will consider the string as an identifier.
AMB
"DXC" wrote:

> What is the difference using the single or double quotes in a stored
> procedure ?
> Ex1: where username = @.username
> AND status = "D" -- or 'D'
> Ex2: if @.stringtype = "D" -- or 'D'
> Select ...............
>
> Thanks.|||Thanks...........
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "SET QUOTED_IDENTIFIER" in BOL.
> Example:
> use northwind
> go
> set quoted_identifier off
> go
> select 1
> where
> " 1111111111111111111111111111111111111111
111111111111111111111111111111111
1111111111111111111111111111111111111111
111111111111111" != '1'
> go
> set quoted_identifier on
> go
> select 1
> where
> " 1111111111111111111111111111111111111111
111111111111111111111111111111111
1111111111111111111111111111111111111111
111111111111111" != '1'
> go
>
> The first "select" statement will return '1', but the second one will give
> an error because sql server will consider the string as an identifier.
>
> AMB
> "DXC" wrote:
>

Quotations in this sql statement

I'm having trouble getting the quotations right in this sql statement because of the single quotes in the displayname. It keeps breaking my application. How would you use quotes to get this to work?

Select description from lawschools_tbl where displayname='Certificat, L'Institut d'études Politiques/'

When you need to use a quote ' in a SQL String, you need to replace the single quote with two single quotes.

Select description from lawschools_tblwhere displayname='Certificat, L''Institut d''études Politiques/'

|||

Actually I was making it too hard. thanks for your help though with the sql syntax.