Wednesday, March 28, 2012

Query with single quotes using OPENROWSET

I'm trying to pass through a SQL statement to an Oracle database using
OPENROWSET. My problem is that I'm not sure of the exact syntax I need
to use when the SQL statement itself contains single quotes.
Unfortunately, OPENROWSET doesn't allow me to use parameters so I can't
get around the problem by assigning the SQL statement to a parameter of
type varchar or nvarchar as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
@.chvSQL)

I tried doubling the single quotes as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
'SELECT *
FROM A
WHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')'
)

But that didn't work. Is there a way out of this?

Thanks,

Bill E.
Hollywood, FLI take it back--doubling the single quotes is working fine now. I
don't know why I had trouble before.

Bill

No comments:

Post a Comment