We have a production environment that uses a linked server dubbed here as AS400 to our iseries os/5.x DB2 running on SQL Server 2000. For a whole set of reasons, these old SQL Sever 2000 boxes are not slated for an upgrade for several months. The short of it is we need to access the return values from a stored procedure. While the following statement works in the SQL Server 2005 test environment, it does not work in the SQL Server 2000 environment.
EXEC ('CALL S10B3161.OURLIB.GETLEVELS()') AT AS400
I then tried issuing an openquery:
select * from openquery(AS400,'CALL S10B3161.OURLIB.GETLEVELS()')
This didn't work in either the mssql 2000 or mssql 2005 environment.
Here is the result:
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=CALL S10B3161.OURLIB.GETLEVELS()'].
Msg 7357, Level 16, State 2, Line 1
Could not process object 'CALL S10B3161.OURLIB.GETLEVELS()'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
I know the linked server is set up correctly in terms of rcp, etc. because executing a simple sql openquery works. This seems to be a stored proc issue. From what I've Goggled, it looks like this issue has cropped up before.
I did try:
select * from openquery(AS400,'SET FMTONLY OFF; CALL S10B3161.OURLIB.GETLEVELS()')
...but got a SQL prepare statement error on the DB2 side on both mssql boxes. No surprises there.
Surely someone must have found a work around for this issue--please advise.