Platform SDK: Transaction Server |
Microsoft SQL Server is an example of a resource manager that supports implicit initiation of MS DTC transactions. A SQL Server implicitly initiated transaction works as follows:
When the work of the transaction is complete, the stored procedure calls the Transact-SQL COMMIT TRANSACTION statement. In response to the COMMIT TRANSACTION call, SQL Server calls the MS DTC Commit method. MS DTC uses the two-phase commit protocol to coordinate commitment of the transaction. Alternatively, the stored procedure could call the Transact-SQL ROLLBACK TRANSACTION statement. In this case, SQL Server calls the MS DTC Abort method to undo the effects of the transaction.
A resource manager that initiates and participates in MS DTC transactions must reside on a system on which the Complete MS DTC Service has been installed. For information on installation and configuration of MS DTC, refer to "Setting Up an MS DTC System," in the MS DTC Administrator’s Guide and Programmer’s Reference.
The following example illustrates how Microsoft SQL Server can be used to implicitly begin an MS DTC transaction. First SP_CONFIGURE is used to enable the Remote Procedure Transactions option.
SP_CONFIGURE “REMOTE PROC TRANS” 1 RECONFIGURE WITH OVERRIDE
Other resource managers may choose to provide a similar means for existing applications to exploit MS DTC compatible.
/*******************************************************/ /* Using the server configuration parameter for */ implicit server-initiated transactions. */ /*******************************************************/ CREATE PROCEDURE change_addr(@au_id varchar(11), @addr varchar(40), @toserver varchar(12) ) AS declare @execstr varchar(200) -- 1. Start a Transaction. This transaction will become -- an MS 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 -- 4. Make a string with the server name and procedure -- to execute select @execstr = @toserver '.pubs..update_addr ' -- 5. Update remote server. -- ( Note that these servers must be added to each other -- via sp_addserver and sp_addremotelogin ) exec @execstr @au_id, @addr -- 6. Commit the MS DTC transaction COMMIT TRANSACTION