How to access a result set

[mydisney]

I am excuting a call to a sp on the db2 platform (linked server) and trying to capture the result set into a temp table and display the results. The temp table is empty allthough the sp is being executed. Any clues???

create table #tempTable (o_drky char(50), o_drdl01 varchar(50))
insert into #temptable (o_drky,o_drdl01) Exec ('Call QGPL.getallcompanies') AT AS400SRV_IBMDASQL_OLEDB
select * from #temptable

Here is my sp (on db2):

create procedure get_all_companies_now
(out o_drky char(10),
out o_drdl01 char(30))
language sql
begin
declare c1 scroll cursor with return for
select drky, drdl01
from vgiprdcom/f0005
where drsy = '00' and drrt = '01';
open c1;
close c1;
end;

 

[gmmastros]

Are you sure the right procedure is getting called?

[tt][blue]
insert into #temptable (o_drky,o_drdl01) Exec ('[!]Call QGPL.getallcompanies[/!]') AT AS400SRV_IBMDASQL_OLEDB

create procedure [!]get_all_companies_now [/!]
[/blue][/tt]

Perhaps you are calling the wrong procedure?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

it must be that _now in the proc name, very confusing indeed

now? like right now?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

also your proc is expecting parameters, I don't see any being passed in

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mydisney]

This was a spelling mistake:

My sql code:

create table #tempTable (o_drky char(10), o_drdl01 char(50))
insert into #temptable (o_drky, o_drdl01) Exec ('Call QGPL.get_all_companies') AT AS400SRV_IBMDASQL_OLEDB
select * from #temptable

My sp on db2:

create procedure get_all_companies
(out o_drky char(10),
out o_drdl01 char(30))
result set 1
language sql
begin
declare c1 cursor with return for
select drky, drdl01
from vgiprdcom/f0005
where drsy = '00' and drrt = '01';
open c1;
set result sets cursor c1;
end;

Looking up the temp table it is empty.

 

[SQLDenis]

any reason you have a cursor instead of a plain vanilla SELECT statement

what about the params in the proc they are not used inside but are not nullable either
are you sure the call doesn't fail?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mydisney]

SQLDenis:

Here is where I execute the sp on db2:

Exec ('Call QGPL.getallcompanies') AT AS400SRV_IBMDASQL_OLEDB

Result: Command(s) completed successfully.

 

[SQLDenis]

but do you get a resultset when you do that by itself

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mydisney]

The db2 passes back a invisible result set most likely to the buffer (this si where my limited knowledge of sequesl server comes in). When I execute the sp using db2 tool (like the iSeries Navgigator) I get back a result set.

 

[SQLDenis]

maybe you need to run this before the call

SET FMTONLY OFF

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mydisney]

Tried this. No results either. Any other ideas?

create table #tempTable (o_drky char(10), o_drdl01 char(50))
SET FMTONLY OFF
insert into #temptable (o_drky, o_drdl01) Exec ('Call QGPL.getallcompanies') AT AS400SRV_IBMDASQL_OLEDB
select * from #temptable

 

[onpnt]

Did you test the connection at all??

Do you get anything from this?

Select * From OpenQuery(linkedservername,'SELECT * FROM syscat.tables')



_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[mydisney]

I used this and got records returned (which is the same table as in my sp).


Select * From OpenQuery(AS400SRV_IBMDASQL_OLEDB,'SELECT * from vgiprdcom.f0005')

 

[onpnt]

The I would post this in the DB2 forum sense there must be problems with the SP. There isn't much more we can do about it beyond testing the linked server entry and returning records. IMO

_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}