Remote Stored Procedure Architecture
Remote stored procedures are a legacy feature of Microsoft® SQL Server™ 2000. Their functionality in Transact-SQL is limited to executing a stored procedure on a remote SQL Server installation. The distributed queries introduced in SQL Server version 7.0 support this ability along with the ability to access tables on linked, heterogeneous OLE DB data sources directly from local Transact-SQL statements. Instead of using a remote stored procedure call on SQL Server 2000, use distributed queries and an EXECUTE statement to execute a stored procedure on a remote server.
An instance of SQL Server 2000 can send and receive remote stored procedure calls to other instances of SQL Server 2000 and SQL Server version 7.0. An instance of SQL Server 2000 can also send and receive remote stored procedure calls to instances of SQL Server version 6.0 or SQL Server version 6.5. A server running SQL Server 2000 can receive remote stored procedure calls from an instance of SQL Server version 4.21a, but the instance of SQL Server 2000 cannot make remote stored procedure calls to the instance of SQL Server version 4.21a. The instance of SQL Server 4.21a cannot recognize the version of the Tabular Data Stream (TDS) used by SQL Server 2000.
Remote Stored Procedure Protocol Optimizations
The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver both make use of a TDS protocol performance optimization originally introduced to support remote stored procedures. The use of this optimization can be seen in SQL Profiler traces.
SQL Profiler traces events in an instance of SQL Server, such as receipt and return of the Tabular Data Stream (TDS) packets sent between applications and an instance of SQL Server. TDS is the application-level protocol defined for SQL Server client/server communications.
When an application sends a Transact-SQL batch for execution, a generic packet for executing SQL is used that shows up in the SQL Profiler trace as SQL:BatchStarting and SQL:BatchCompleted events. When one instance of SQL Server sends a request for another instance of SQL Server to execute a remote stored procedure, a specialized RPC TDS packet is used. The RPC packet is tailored to the needs of transmitting requests to execute a stored procedure. The relational engine also recognizes that this is a specialized packet and implements a number of optimizations that speeds the execution of the stored procedure. These show up in a SQL Profiler trace as RPC:Starting and RPC:Completed events.
SQL Server 2000 does not limit the use of these specialized RPC packets to server-to-server communications. The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver use this specialized RPC packet to increase performance in two cases:
- If an application uses the ODBC CALL syntax to execute a stored procedure.
- When the provider or driver internally generate calls to system stored procedures.
Users analyzing SQL Profiler traces from applications using the provider or driver can see these RPC TDS events.