Remote stored procedure events deserve particular attention for their flexibility and ease of use. They are especially well-suited for enhancements to SQL Server applications that integrate clients and servers into existing environments.
Open Data Services remote stored procedure events correspond to SQL Server remote stored procedures. Remote stored procedures allow a client connected to one SQL Server to execute a stored procedure on another SQL Server without establishing a client connection to the second server. The server to which the client is connected accepts the client request and sends the request to the remote server on the client's behalf. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.
Remote stored procedure requests can be initiated by clients or servers. DB-Library and ODBC functions are available for calling remote stored procedures directly from a client. For more information about remote stored procedures, see ODBC Drivers for SQL Server and Open Data Services the Microsoft SQL Server Transact-SQL Reference, and Microsoft SQL Server Programming DB-Library for C.
Remote stored procedures are particularly powerful when combined with Open Data Services. SQL Server clients and servers can issue remote stored procedure calls to an Open Data Services server application. The following illustrations show a connection between a client, SQL Server, and Open Data Services using remote stored procedures.
To Open Data Services, requests from SQL Server clients and servers look the same ¾ they simply activate a remote stored procedure event ¾ so you can create a single event handler that responds to requests coming from a client or to requests coming from a SQL Server database server (as the result of a trigger, for example). Moreover, event handlers for remote stored procedure events are generally easier to write than language event handlers, since the incoming commands and parameters don't have to be parsed from a command string. For more information about event handlers for remote stored procedures, see Chapter 3, Open Data Services Programming.