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.
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 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. However, you can
get the remote stored procedures to execute within the local
transaction context by 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 -- a remote procedure
COMMIT TRANSACTION
SQL Server also automatically promotes a local transaction to a distributed transaction if the remote proc trans
option is enabled and a remote stored procedure is invoked in a
transaction. This option can be configured globally in SQL Server via sp_configure, or it can be set explicitly at the connection level with the SET REMOTE_PROC_TRANSACTIONS command. If the remote proc trans
option is enabled, remote stored procedure calls in local transactions
are automatically protected as part of distributed transactions, without
requiring you to rewrite applications to specifically issue BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.
