| Russell Fleming 2005-08-19, 9:24 am |
| ASA 9.0.2.2451
I'm trying to execute a stored procedure and populate a global temporary
table with the result set. I must be using incorrect methods because it
doesn't work. The basic goal is to execute a stored procedure which returns
data which includes additional data from one or more remote servers. Here is
what I tried:
declare @sql varchar(200);
insert into gt_myglobal
select * from pr_myproc( ) ;
for servers as cur_servers cursor for
select srvname from sys.sysservers
do
set @sql = 'insert into gt_myglobal select * from pr_myproc@' || srvname
|| '()'
execute immediate @sql ;
end for;
select * from gt_myglobal ;
Please assume I have created proxy procedures with the naming convention
{procedure name}@{remote server name}. Each procedure declaration has the
identical result set specified as the global temp tables schema. On
execution I get undescriptive errors like some column name not found. The
column name in the message is not in the result set description.
I can execute each procedure (the local and proxy) and get the result set.
I've also tried:
select * from pr_myproc( )
union all select * from pr_myproc@servername
( )
and it works fine. I appear to be unable to get this to work within another
stored procedure. I either need this SQL to insert into the table or have
execute immediate return a result set. Don't have a preference.
Help?
Russ
|