Calling Procedure and Link Server using Distributed Transactions

Distributed Transactions

A transaction within a single instance of the Database Engine that spans two or more databases is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local transaction.

 A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).

Prepare phase

When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the transaction. Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager.

Commit phase

If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. The resource managers can then complete the commit. If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the application. If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.

Database Engine applications can manage distributed transactions either through Transact-SQL or the database API.

Transact-SQL Process

The distributed transactions started in Transact-SQL have a relatively simple structure:

1. A Transact-SQL script or application connection executes a Transact-SQL statement that starts a distributed transaction.
2. The instance of the SQL Server Database Engine executing the statement becomes the controlling server in the transaction.
3. The script or application then executes either distributed queries against linked servers or remote stored procedures against remote servers.
4. As distributed queries and remote procedure calls are made, the controlling server automatically calls Microsoft Distributed Transaction Coordinator (MS DTC) to enlist the linked and remote servers in the distributed transaction.
5. When the script or application issues either a COMMIT or ROLLBACK statement, the controlling instance of SQL Server calls MS DTC to manage the two-phase commit process or to notify the linked and remote servers to roll back their transactions.

Transact-SQL Statement

The Transact-SQL statements controlling the distributed transactions are few because most of the work is done internally by the SQL Server Database Engine and MS DTC. The only Transact-SQL statements required in the Transact-SQL script or application are those required to:

·         Start a distributed transaction.

·         You need to enable rpc and rpcOut options if you are using link server.

·         Perform distributed queries against linked servers or execute remote procedure calls against remote servers.

·         Call the standard Transact-SQL COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to complete the transaction.

Transact-SQL Sample

Step1: Configure your remote Procedure transaction. You need to enable it on both databases

SP_CONFIGURE 'REMOTE PROC TRANS', 1

RECONFIGURE WITH OVERRIDE


Step2: Change following options from “False” to “True” in link server settings

EXEC master.dbo.sp_serveroption @server=N'192.168.25.26', @optname=N'rpc', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'192.168.25.26', @optname=N'rpc out', @optvalue=N'true'

GO


To understand detail about link server please have a look at this msdn link
Step2: Change following options from “False” to “True” in link server settings


http://msdn.microsoft.com/en-us/library/ff772782.aspx

Step3:
a). Create your procedure on first database as per your requirement. You can call query directly by using AT clause to other database like this

Declare @vstr nvarchar ( 4000);

set @vstr = 'delete from System.dbo.Lookup where id in  (1,2,3)’

exec (@vstr ) AT [LinkServer]


b). Create your procedure on first and second database as per your requirement. You can call procedure from one database to other as follows

Declare @vstr nvarchar ( 4000);

set @vstr = 'System.dbo..update_addr '

exec @vstr p1,p2 -- ParameterValues

Step4: You must apply transaction. Distributed transaction can be applied specifically with Distributed word if not mention. Engine will automatically check weather this transaction is distributed or not and it applies distributed transaction at its own. Complete sample code is as follows

===========================================================================

Create PROCEDURE [dbo].[DeleteUsers]

      @UserID     int

AS

BEGIN

      SET NOCOUNT ON;

Declare @vstr nvarchar ( 4000);

-- Start distributed Transaction

BEGIN DISTRIBUTED TRANSACTION; 

-- 2. Change local author information

delete from  [DeleteUsers] where userid in (@UserID)           

-- 3. Delete data from remote server using link server

set @vstr = 'delete from System.dbo.Lookup where id in  ' + cast(@UserID as nvarchar(15))

exec (@vstr ) AT [LinkServer] 

-- 4. Commit the DTC transaction

      COMMIT TRANSACTION;    

           

END

===========================================================================

If you want to look at the example to call procedure look at the code below or go to this link

http://msdn.microsoft.com/en-us/library/windows/desktop/ms687099(v=vs.85).aspx

============================================================================

/*******************************************************/

/* Using the server configuration parameter for    */

/* implicit server-initiated transactions.       */

/*******************************************************/

CREATE PROCEDURE change_addr(@au_id varchar(11),   -- author ID

               @addr varchar(40),    -- new address

               @toserver varchar(12) ) -- server name

AS -- procedure follows

declare @execstr varchar(200)

-- 1. Start a Transaction. This transaction will become

--  a DTC distributed transaction when the remote

--  stored procedure is called because the server

--  configuration parameter is set.

BEGIN TRANSACTION

-- 2. Change local author information 

update authors set address = @addr where au_id = @au_id

-- 3. Make a string with the server name and procedure

--  to execute 

select @execstr = @toserver '.pubs..update_addr '

-- 4. Update remote server.

--  ( Note that these servers must be added to each other

--   via sp_addserver and sp_addremotelogin )

exec @execstr @au_id, @addr 

-- 5. Commit the DTC transaction 

COMMIT TRANSACTION

/*******************************************************/

/* Stored procedure to update an author's address on  */

/* each remote server.                 */

/*******************************************************/

CREATE PROCEDURE update_addr(@au_id varchar(11),   -- author ID

               @addr varchar(40) )   -- new address

AS -- procedure follows

update authors set address = @addr where au_id = @au_id

===========================================================================