When the setting for the REMOTE_PROC_TRANSACTIONS session-level option is not specified, it is based on the value of the REMOTE PROC TRANS server configuration parameter.
| REMOTE PROC TRANS | REMOTE_PROC_TRANSACTIONS |
|---|---|
| 0 | OFF |
| 1 | ON |
By default, the value of the REMOTE PROC TRANS server configuration parameter is 0. This ensures compatibility with earlier SQL Server releases, which did not support distributed transactions. The value of the REMOTE PROC TRANS server configuration parameter can be changed by using the sp_configure system stored procedure. The RECONFIGURE WITH OVERRIDE statement must be entered after the parameter value is changed. You can set the value of REMOTE PROC TRANS as follows:
sp_configure 'remote proc trans', 1 reconfigure with override
Only a SQL Server system administrator can enter the RECONFIGURE WITH OVERRIDE statement.
The REMOTE PROC TRANS server configuration parameter is dynamic. For new SQL Server sessions, the REMOTE_PROC_TRANSACTIONS session-level option is set based on the current value of REMOTE PROC TRANS.
For details about REMOTE PROC TRANS, see the sp_configure in What's New in SQL Server 6.5.
The following example shows how an MS DTC distributed transaction can be used with an existing Transact-SQL application to ensure that two SQL Servers are updated consistently. SQL Server implicitly initiates the distributed transaction when the remote stored procedure is called and the REMOTE PROC TRANS server configuration parameter is ON. Note that the Transact-SQL application continues to use the BEGIN TRANSACTION statement rather than the new BEGIN DISTRIBUTED TRANSACTION statement.
The SQL Server system administrator first uses sp_configure to enable the REMOTE PROC TRANS server configuration parameter:
SP_CONFIGURE 'REMOTE PROC TRANS', 1
RECONFIGURE WITH OVERRIDE
/*******************************************************/
/* Using the server configuration parameter for */
/* implicit SQL Server initiated transactions. */
/*******************************************************/
CREATE PROCEDURE change_addr(@au_id varchar(11),
@addr varchar(40),
@toserver varchar(12) ) AS
declare @execstr varchar(200)
/* Begin a local SQL Server Transaction. */
/* This transaction will become an MS DTC distributed */
/* transaction when the remote stored procedure is */
/* called because the REMOTE PROC TRANS server */
/* configuration parameter is ON. */
BEGIN TRANSACTION
/* Change local author information */
update authors set address = @addr where au_id = @au_id
/* Make a string with the remote server name and */
/* stored procedure to execute */
select @execstr = @toserver + '.pubs..update_addr '
/* Update remote server. This statement causes SQL */
/* Server to begin an MS DTC distributed transaction. */
/* (Note that these servers must be added to each */
/* other by using sp_addserver and sp_addremotelogin.) */
exec @execstr @au_id, @addr
/* Commit the MS DTC transaction */
COMMIT TRANSACTION
The following remote stored procedure is used to update the remote pubs database.
/********************************************************/
/* Stored procedure to update an author's address on */
/* the remote server. */
/********************************************************/
CREATE PROCEDURE update_addr(@au_id varchar(11),
@addr varchar(40)) AS
update authors set address = @addr where au_id = @au_id