Remote Stored Procedures

You can execute a stored procedure residing on another server by using a four-part naming scheme:

EXEC server_name.db_name.owner_name.proc_name 

This concept is called remote stored procedures (RPCs). The name implies that the procedure called on the other server is a special type of stored procedure, but it is not. Any stored procedure can be called from another server as long as the remote server has been configured and the appropriate login mapping has been done. The method used to set up servers to allow remote procedure calls is described in Chapter 19, "Managing Linked and Remote Servers."

The processing done by the remote stored procedure is, by default, not done in the local transaction context. If the local transaction rolls back, modifications performed by the remote stored procedure are not undone. You can get the remote stored procedures to execute within the local transaction context using distributed transactions, as in the following example:

BEGIN DISTRIBUTED TRANSACTION 
EXEC purge_old_customers  --A local procedure
EXEC LONDON.customers.dbo.purge_old_customers
COMMIT TRANSACTION

Distributed transactions and the Microsoft Distributed Transaction Coordinator (DTC) service are discussed in Chapter 32, "Distributed Transaction Processing."