Microsoft SQL Server 2008 R2 Unleashed- P182:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1764 CHAPTER 44 Advanced Stored Procedure Programming and Optimization performance issues. In some cases for example if the result set is large and accessed repeatedly performance may improve when you use temporary tables instead because they support indexes and statistics. Using Remote Stored Procedures You can execute a stored procedure residing on another server by using a four-part naming scheme EXEC 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 method used to set up servers to allow Remote Procedure Calls RPCs is described in Chapter 54 Managing Linked and Remote Servers on the CD . 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 -- 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
