This procedure allows an application to execute a SQL statement on a remote server and retrieve any result sets generated
by that statement. The SQL statement is sent verbatim to the remote server and therefore SQL Anywhere does not need to able
to parse the statement.
To use this system procedure, you must define the remote server with the CREATE SERVER statement.
Unlike the FORWARD TO statement, sp_forward_to_remote_server can be used within procedures. However, this stored procedure
cannot be used within the FROM clause of a SELECT statement since the schema of the remote result sets is arbitrary. You can
fetch remote result sets by declaring a cursor on a stored procedure that is called in the sp_forward_to_remote_server procedure.
If the SQL statement returns multiple result sets, the sp_forward_to_remote_server stored procedure returns each remote result
set in turn.
CREATE SERVER rem CLASS 'saodbc' USING 'driver=SQL Anywhere 12 ;
eng = mytest ; dbn = testdb2 ; links = SharedMemory'
Assume that external logins are not needed. Create the local function that calls sp_forward_to_remote_server.
CREATE FUNCTION fetch_num_remote_tables ( IN server char(128) ) RETURNS int
DECLARE num_tables int;
DECLARE curs CURSOR FOR CALL sp_forward_to_remote_server
( server, 'SELECT COUNT(*) FROM sys.systable' );
FETCH next curs INTO num_tables;
Fetch the number of tables from the remote server.