Friday, March 30, 2012

OPENQUERY question

I'd like to use a linked server to a Setcim database to retrieve results fro
m
a procedure in the Setcim database.
The syntax used to call the view in Setcim is:
start record 'BSW1View'
which works when using the SQLplus tools for Setcim.
I was assuming I could OPENQUERY to call this record and then put the data
into a SQL Server database but I get syntax errors when I try various
combinations of the following:
SELECT *
FROM OPENQUERY(PRISM,'start record 'BSW1View'')
The issue appears to be the single quotes around the procedure name.
Can I do what I want using OPENQUERY? If not, what options are available?
Thanks in advance,
RaulDouble them (the inner apostrophes).
SELECT *
FROM OPENQUERY(PRISM,'start record ''BSW1View''')
AMB
"Raul" wrote:

> I'd like to use a linked server to a Setcim database to retrieve results f
rom
> a procedure in the Setcim database.
> The syntax used to call the view in Setcim is:
> start record 'BSW1View'
> which works when using the SQLplus tools for Setcim.
> I was assuming I could OPENQUERY to call this record and then put the data
> into a SQL Server database but I get syntax errors when I try various
> combinations of the following:
> SELECT *
> FROM OPENQUERY(PRISM,'start record 'BSW1View'')
> The issue appears to be the single quotes around the procedure name.
> Can I do what I want using OPENQUERY? If not, what options are available?
> Thanks in advance,
> Raul
>|||Your suggestion worked. The only problem is I got the following error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'start record 'BSW1View''. The OLE DB provider
'MSDASQL' indicates that the object has no columns.
I'll modify the procedure on the Setcim side and try again.
Thanks for the help,
Raul
"Alejandro Mesa" wrote:
> Double them (the inner apostrophes).
> SELECT *
> FROM OPENQUERY(PRISM,'start record ''BSW1View''')
>
> AMB
> "Raul" wrote:
>

No comments:

Post a Comment