sp_forward_to_remote_server system procedure

sp_forward_to_remote_server (
  server_name,
  sql
)

server_name Use this CHAR(128) parameter to specify the name of the remote server the SQL statement is executed on.
sql Use this LONG VARCHAR parameter to specify the SQL statement to execute on the remote server.

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.

Note

If the SQL statement returns multiple result sets, the sp_forward_to_remote_server stored procedure returns each remote result set in turn.

None

There are no local side effects to executing this stored procedure; however, since the SQL statement that is executed on the remote server is arbitrary, there may be side effects on the remote server.

The following example uses the sp_forward_to_remote_server stored procedure and a local function to determine the number of tables that a remote SQL Anywhere 12 database has.

Run the following command to create two databases named testdb1 and testdb2:
dbinit testdb1 dbinit testdb2
Start both databases with the same SQL Anywhere server.
dbeng12 -n mytest testdb1 testdb2

Connect to testdb1 from Interactive SQL.

dbisql -c "uid=DBA;pwd=sql;server=mytest;dbn=testdb1;links=SharedMemory"

Create a remote data access server on testdb2.

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
     BEGIN
          DECLARE num_tables int;
          DECLARE curs CURSOR FOR CALL sp_forward_to_remote_server 
             ( server, 'SELECT COUNT(*) FROM sys.systable' );
          OPEN curs;
          FETCH next curs INTO num_tables;
          CLOSE curs;
          RETURN num_tables;
     END

Fetch the number of tables from the remote server.
SELECT fetch_num_remote_tables( 'rem' )

sp_forward_to_remote_server system procedure

Syntax

Arguments

Remarks

Note

Permissions

Side effects

See also

Example