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

Posted on 2011-10-17
Last Modified: 2012-05-12

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.


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.

Question by:ditallop
    LVL 34

    Expert Comment

    by:Gary Patterson
    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

    Author Comment



    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.
    LVL 34

    Accepted Solution

    I think the answer is "you can't do that through a linked server in SQL Server 2000", based on what I read here:

    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.

    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 */

    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

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now