Work Around: Executing DB2 Stored procedures from SQL Server 2000 Linked Server

Techies--

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.






Paula DiTalloIntegration developerAsked:
Who is Participating?
 
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
I think the answer is "you can't do that through a linked server in SQL Server 2000", based on what I read here:

http://www.sqlmag.com/forums/aft/72331
http://www.sqlmag.com/forums/aft/72222

and here (you'll need to have a valid IBM account with support entitlement, or you can contact IBM Support or an IBM Business Partner for help obtaining the article.

http://www-01.ibm.com/support/docview.wss?crawler=1&uid=nas1f0177f01ae69f0a886256aa1007f5a0d

So, what can you do?  This problem only exists due to the "reformatting" that SQL Server 200 does to the query.  Of course, we could use a different interface to execute the stored proc, retrieve the return value, and get it someplace usable, but one simple solution might be to create a UDF that encapsulates the stored procedure call, then run a query that invokes the UDF:

Something like this:

CREATE FUNCTION GETLEVELS() RETURNS CHAR(7)  /* or whatever you need to return */
LANGUAGE SQL
BEGIN
   RETURN(CALL OURLIB.GETLEVELS());
END

If your GETLEVELS stored proc is an external stored proc, you might just want to create a UDF version instead or wrapping a UDF around the stored proc.

Then use a dummy query of some sort to invoke the function:

select getlevels() from somelib.sometable
  fetch first 1 row only;

- Gary Patterson







- Gary Patterson
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Openquery executes a pass-through query on the linked database server (in this case the AS/400), and as a result needs to be specified in AS/400 DB2 SQL syntax.  

Try this:

select * from openquery(AS400,'CALL OURLIB.GETLEVELS()')

- Gary Patterson
0
 
Paula DiTalloIntegration developerAuthor Commented:

Gary,

This returns the same ole db error message: The ole db provider 'msdasql' indicates that the object has no columns.

For some reason, on SQL 2000 it thinks instead of calling a stored proc, the request truly is select related. This works fine on our SQL 2005/2008 boxes.
0
 
Paula DiTalloIntegration developerAuthor Commented:
Nice solution Gary, I so much like your approach. I will give this a try. I came to the same conclusion you did regarding the impasse with SQL 2000.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.