SQL Server 2008 : Using Remote Stored Procedures

4/28/2011 4:26:15 PM
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:

EXEC purge_old_customers --A local procedure
EXEC LONDON.customers.dbo.purge_old_customers -- a remote procedure

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.

GTS - youtube channel
Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us