[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-17
4
Medium Priority
?
1,504 Views
Last Modified: 2012-05-12
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.






0
Comment
Question by:Paula DiTallo
  • 2
  • 2
4 Comments
 
LVL 36

Expert Comment

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

Author Comment

by:Paula DiTallo
ID: 37002443

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
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 2000 total points
ID: 37003473
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
 

Author Comment

by:Paula DiTallo
ID: 37008851
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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
Suggested Courses

834 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