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."



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net