Remote Procedures as proxy tables

An optional clause may be added to the create existing table statement to indicate the remote object is actually a stored (or other) procedure instead of a table. Without this clause, the remote object is assumed to be a table or view:

create existing table t1
	(
		column_1		int,
		column_2		int
	)
	EXTERNAL PROCEDURE AT "SERVER_A.mydb.dbo.p1"

In the case where the remote object is type procedure, several processing differences occur:

Component Integration Services users can map remote or external objects of the type rpc to local proxy tables. If a table is created in this way, it can be referenced only by the select and drop commands. The commands insert, delete, and update generate error messages, since the table is assumed to be read-only. Proxy definitions should only be created for procedures which return data.

If an object of the type rpc has been defined within the server, a query is not issued to the remote server on which the object resides. Instead, the server issues an RPC and treats the results from the RPC as a read-only table.

Examples

create existing table rtable
 (	col1		int,
 	col2		datetime,
 	col3		varchar(30)
 )
external procedure at “RMTSERVER...myproc “

select * from rtable

When this query is issued, the server sends the RPC named myproc to server RMTSERVER. Row results are treated like the results from any other table; they can be sorted, joined with other tables, grouped, inserted into another table, and so forth.

RPC parameters should represent arguments that restrict the result set. If the RPC is issued without parameters, the entire result set of the object is returned. If the RPC is issued with parameters, each parameter further limits the result set. For example, the following query:

select * from rtable where col1 = 10

results in a single parameter, named @col1, that is sent along with the RPC. Its value is 10.

Component Integration Services attempts to pass as many of the search arguments as possible to the remote server, but depending on the SQL statement being executed, Component Integration Services might perform the result set calculation itself. Each parameter represents a search for an exact match, for example, the = operator.

The following are rules which define the parameters sent to the RPC. If an RPC is used as a Component Integration Services object, these rules should be kept in mind during development.