I have db2/400 set up as linked server and need to call a sp that returns company codes. After executing the sp I do not see any resultset in Sequel Server even if I am trying to load the result set into a temp table. I know the connection is working beacuse when I do a straight select it brings back records. Also I have tested the sp using iSeries navigator and it brings back records. At this point I'm not sure where the problem is. Maybe my stored procedure is wrong?? Can anyone help please....
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;
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;