Re: Calling DB2 SQL Procedure from SQL Server
From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 08/31/04
- Next message: oj: "Re: I need a solution for recursion"
- Previous message: Ilya Margolin: "Re: Pattern Search Column using something like a regular expression"
- In reply to: Darrell Davis: "Re: Calling DB2 SQL Procedure from SQL Server"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 31 Aug 2004 21:41:09 +0000 (UTC)
Darrell Davis (DarrellDavis@discussions.microsoft.com) writes:
> I'm new to DB2 procedures also, but I believe the procedure works. I
> took it from an example tutorial. Apparently, the DECLARE C1 CURSOR
> WITH RETURN statement indicates that the rows in the cursor C1 will be
> returned from the procedure. Then, apparently, the OPEN C1 statement is
> all that is required to complete the work. When I execute the procedure
> from the DB2 Navigator query tool, it works correctly.
>
> Any other suggestions? I think I am close to getting a solution to my
> problem if I can only figure out how to call the procedure from SQL
> Server.
Rather than using OPENQUERY(), you could try to call the query this way:
EXEC LINKEDSERVER..NFUHPLIBR.CRPATEST1
and see if this gives you any data back.
I realise that you want to use OPENQUERY, so that you can use the
result in a query, but this is a way to see if SQL Server finds a result
set. And if nothing else works, you could say:
INSERT #temp (col)
EXEC LINKEDSERVER..NFUHPLIBR.CRPATEST1
and then use the temp table.
I don't know about DB2, but you can run into this problem with
OPENQUERY when the linked server is another SQL Server too. The typical
case then is that the stored procedure creates a temp table and then
returns a result set from the temp table. When OPENQUERY invokes SQLOLEDB,
it first runs the query with SET FMTONLY ON, which causes SQL Server to
just sift through the statements to find the result sets, but not execute
the statements. Because of this, the temp table is never created, and no
result set is found.
I have no idea, but it could be something like this that is going here
as well. How you should change the procedure to address that, you are
probably better off asking in a DB2 forum. :-)
-- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: oj: "Re: I need a solution for recursion"
- Previous message: Ilya Margolin: "Re: Pattern Search Column using something like a regular expression"
- In reply to: Darrell Davis: "Re: Calling DB2 SQL Procedure from SQL Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
