Accessing Result Set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Accessing Result Set

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-10-24 : 09:10:14

Using Sequel Server 2005, I am calling a stored procedure on db2 The sp is passing back a result set and my question is: How do I access the result set, I assume it is being buffered. Does anyone have reference to some litterature that will explain the process?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 09:18:29

[code]insert into #temp ( <col list>) exec sp_name[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 09:21:29

quote:
Originally posted by khtan

insert into #temp ( <col list>) exec sp_name

KH
[spoiler]Time is always against us[/spoiler]

Provided SP returns single resultset

Madhivanan

Failing to plan is Planning to fail

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-10-24 : 11:08:12

Thank you.

This is the code I have in Sequel Server calling the db2 using linked server:

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

When I look up the temp table it is empty allthough the sp is being executed and there are records to be selected. Any clues

Here is my sp on db2 (not that this matter I think):

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources