help calling stored proc on linked server

[ddiamond]

I have a linked server pointing to DB2 on AS400. I am trying to call a stored proc on the AS400 from SQL Server 2008. I first tried:

select * from openquery ( ADM_AS400_LPAR, 'CALL XGGNDTAADM.EFILE_GET_QUICK_SEARCH (''C007984A'')')

I get the error message: The OLE DB provider "IBMDA400" for linked server "ADM_AS400_LPAR" indicates that either the object has no columns or the current user does not have permissions on that object.

It is definately not a permissions issue.

I then tried the 4 part syntax.

EXEC ADM_AS400_LPAR.EMJ_TST.XGGNDTAADM.EFILE_GET_QUICK_SEARCH 'C007984A'

Error message: OLE DB provider "IBMDA400" for linked server "ADM_AS400_LPAR" returned message "SQL0104: Token { was not valid... A syntax error was detected at token {.

I ran a trace on the AS400 to see exactly what the linked server passed to it. It passed the following:

{?=call "EMJ_TST"."XGGNDTAADM"."EFILE_GET_QUICK_SEARCH";1(?)}

Well, that is certainly not valid DB2 syntax, but I have no idea why the linked server generated that syntax. Does anyone have any suggestions?

 

[SQLBill]

I don't know about calling a stored proc on the AS400, but this is what we use to Select data from the AS400.

select * INTO <tblename> from openquery(<servername>, 'select * from <schema>.<table>')

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ddiamond]

Thanks Bill. Select statements with openquery have always worked fine. But I'm now trying to see if I can call stored procedures.