I'm attempting to do some datamart loading in a proc using a cursor. I'm tripped up on my table creates, truncates, and drops. They seem to be causing a 'cursor not found' error.
I read through my table of tables and process:

1. create proxy table to other db
2. clear datamart table
3. load datamart table from proxy
4. drop proxy

The natural question might be why am I just not using proxy tables in my datamart. But for performance issues, we prefer to load from the remote db in nightly processing. Is there a way to define my cursor to allow this?

begin
  declare @sql varchar(500);
  /*create temporary proxies to reggie database to load data*/
    declare SourceTab scroll cursor for select tabledatabase, tabletable, tableproxyprefix, tableremoteserver, tableremoteowner, tablelocalowner
     from dmartstu.sourcetable where tabledatabase=@dbselect
        order by tabledatabase, tableorder asc;
    /****************/
    /*temp variables*/
    /****************/
    declare tdatabase char(50);
    declare ttable char(100);
    declare tproxprefix char(50);
    declare tremoteserver char(50);
    declare tremoteowner char(50);
    declare tlocalowner char(50);
    declare @remotetablename varchar(100);
    declare @sourcetablename varchar(100);
    declare @receivetablename varchar(100);

    open SourceTab;

    TabLoop: loop
      fetch next SourceTab into tdatabase, ttable, tproxprefix, tremoteserver, tremoteowner, tlocalowner;
      if sqlstate <> 0 then
        message 'sqlstate:  ' || sqlstate to client;
        leave TabLoop
      end if;
      set @remotetablename=trim(tremoteserver)||';;'||trim(tremoteowner)||';'||ttable;      /*remote server table name - ex: RO_Reggie;;dba;classes*/
      set @sourcetablename=trim(tlocalowner)||'.'||trim(tproxprefix)||'_'||trim(ttable);    /*proxy table name - ex: dmartstu.ProxReg_classes*/
      set @receivetablename=trim(tlocalowner)||'.'||trim(tdatabase)||'_'||trim(ttable);     /*actual (receiving) table name ex: dmartstu.registrar_classes*/
      set @sql='drop table if exists '||@sourcetablename||';';
      execute immediate @sql;
      set @sql='create existing table '||@sourcetablename||' AT '''||@remotetablename||''' ;';
      execute immediate @sql;
      set @sql='truncate table '||@receivetablename||';';
      execute immediate @sql;
      set @sql='insert into '||@receivetablename||' select * from '||@sourcetablename||';';
      execute immediate @sql; 
      set @sql='drop table '||@sourcetablename||';';
      execute immediate @sql;

     message 'loop end or back' to client

    end loop TabLoop;

    close SourceTab;
end