Generic Query Designer quotations -- sql server reporting services

dachrist
9/24/2004 8:03:06 AM

Double quotes are required. It is an Oracle database, I am using the OLE DB
provider.

[quoted text, click to view]

Brian Welcker [MSFT]
9/25/2004 9:34:24 PM

It should work fine. Unless you are building an expression, quotes should
not matter. Have you tried it with the graphical one?

--
Brian Welcker
Group Program Manager
SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

dachrist
9/27/2004 10:05:05 AM

I need to build the query in the generic designer, because I need to create
dynamic parameters. I don't believe I can do that in the graphical designer?
The actual query works fine in the graphical designer.

[quoted text, click to view]

dachrist
9/27/2004 2:41:05 PM

Yes, I have done that and confirmed that I am using the correct syntax. I
think the problem is that Oracle requires any field that starts with a number
to have a quote around it to designate it as a string. These quotes are what
is causing me problems in the Generic query designer, because I want to add
dynamic parameters to the query.

[quoted text, click to view]

Bruce Loehle-Conger
9/27/2004 2:53:01 PM

If you are building the SQL dynamically then I suggest you back up and make
sure you are creating the appropriate syntax. Set the source of a textbox to
the expression you are currently doing in the generic designer. Then you can
copy and paste that into whatever Oracle calls their tool to execute ad hoc
queries. Then you can quickly find out whether the syntax is correct.
Consider that the generic designer essentially allows passthrough queries.
You need the exact same syntax that you would have if you were going
directly against Oracle.

Bruce L-C

[quoted text, click to view]

Donovan R. Smith [MS]
9/27/2004 8:00:51 PM

[quoted text, click to view]

I'm sure there's an Oracle equivalent of SQL Profiler. Would you run
that tool or NetMon and see whether the generic query designer is
passing the appropriate string to Oracle?

--
Thanks.

Donovan R. Smith
Software Test Lead

dachrist
9/28/2004 8:17:03 AM

This is the statement that works:

SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
"524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
"524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
"524ALPRXD2_COND", "524TLPRXD2_TEMP",
"524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
"524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
"565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
"565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
"524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
"631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)

What I want to do is put in a couple of dynamic parameters. But I add the
=" at the beginning of the statement, and end the statement with ", the query
will not run, with the error "The expression for the query â€˜PRPQISâ€™ contains
an error: [BC30205] End of statement expected." If I change the double
quotes within the statement to single quotes, I get an Oracle error,
ORA-00936: Missing Expression. If I remove the quotes all together, I don't
get anything returned from my query.

[quoted text, click to view]

Bruce Loehle-Conger
9/28/2004 8:55:56 AM

Could you post the statement that you have in the Generic query designer?
That might help generate some ideas on what is going on.

Bruce L-C

[quoted text, click to view]

dachrist
9/28/2004 11:29:05 AM

Thanks Bruce! That did it.

[quoted text, click to view]

Bruce Loehle-Conger
9/28/2004 11:30:40 AM

It looks to me that you are coming up against the typical problem of
embedding double quotes in a string. As a coincidence today I have just been
dealing with creating a dynamic sql statement in a stored procedure which
had the same issue but with single quotes. As I mentioned before, the best
way to deal with making sure that your expression is correct is to back off
from trying to create the dataset and first make sure the correct string is
being created. You should just assign the expression to a text box (I create
a report with nothing on it but a textbox).

For instance in your case I started off with this expression for a textbox.

="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","

i.e I did what you said you did, added an =" and finished off with a ".
Trying to go into preview it fails with an error. Which is what I expected
would happen. You can not put in double quotes in a string without following
the special string rule, do it twice.

I assigned this string to the textbox:

="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "

Notice that everywhere you want your resulting string to have double quotes
I put double quotes twice. In preview the textbox has this:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",

Just what you want. When testing this your textbox needs to end up with
exactly the string expected by Oracle. Once you have this then you can
assign the expression to the dataset and away you go.

Bruce L-C

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Generic Query Designer quotations