Remote stored procedures are a legacy feature of Microsoft® SQL Server™. 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 7.0, use distributed queries and an EXECUTE statement to execute a stored procedure on a remote server.
In addition to still supporting the remote stored procedures from existing applications, the OLE DB Provider for SQL Server and the SQL Server ODBC driver both make use of a performance optimization of remote stored procedures, and this shows up in SQL Server Profiler traces.
SQL Server Profiler traces events in an instance of SQL Server, such as receipt and return of Tabular Data Stream (TDS) packets sent between applications and the 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 Server 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 Server Profiler trace as RPC:Starting and RPC:Completed events.
The specialized RPC packet is not limited to use in server-to-server communications. The OLE DB Provider for SQL Server and the SQL Server ODBC driver take advantage of this specialized RPC packet to increase performance in two cases:
Users analyzing SQL Server Profiler traces can see these RPC entries from the provider and driver.
A server running SQL Server 7.0 can send and receive remote stored procedure calls to other version 7.0 servers. A server running SQL Server 7.0 can also send and receive remote stored procedure calls to servers running either version 6.0 or 6.5. A server running SQL Server 7.0 can receive remote stored procedure calls from a server running version 4.21a, but the server running version 7.0 cannot make remote stored procedure calls to the server running version 4.21a. The 4.21a server cannot recognize the version of the Tabular Data Stream (TDS) used by SQL Server 7.0.
| Configuring Remote Servers | TSQL Event Category |
| Calling a Stored Procedure (OLE DB) | Calling a Stored Procedure |