Skip to content

Latest commit

 

History

History
80 lines (51 loc) · 3.81 KB

configure-the-remote-proc-trans-server-configuration-option.md

File metadata and controls

80 lines (51 loc) · 3.81 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Server configuration: remote proc trans
Find out about the remote proc trans option. See how it helps protect the actions of a server-to-server procedure through an MS DTC transaction.
rwestMSFT
randolphwest
10/18/2024
sql
configuration
conceptual
remote proc trans option
distributed transactions [SQL Server], enforcing

Server configuration: remote proc trans

[!INCLUDE SQL Server]

This article describes how to configure the remote proc trans server configuration option in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql]. The remote proc trans option helps protect the actions of a server-to-server procedure through a [!INCLUDE msCoName] Distributed Transaction Coordinator (MS DTC) transaction.

Set the value of remote proc trans to 1 to provide an MS DTC-coordinated distributed transaction that protects the ACID (atomic, consistent, isolated, and durable) properties of transactions. Sessions begun after setting this option to 1 inherit the configuration setting as their default.

Important

[!INCLUDE ssNoteDepFutureAvoid]

Prerequisites

Remote server connections must be allowed before this value can be set.

Recommendations

This option is provided for compatibility with earlier versions of [!INCLUDE msCoName] [!INCLUDE ssNoVersion] for applications that use remote stored procedures. Instead of issuing remote stored procedure calls, use distributed queries that reference linked servers, which are defined by using sp_addlinkedserver.

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Use SQL Server Management Studio

  1. In Object Explorer, right-click a server and select Properties.

  2. Select the Connections node.

  3. Under Remote server connections, select the Require Distributed Transactions for server to server communication check box.

Use Transact-SQL

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the remote proc trans option to 1.

    USE master;
GO

EXECUTE sp_configure 'remote proc trans', 1;
GO

RECONFIGURE;
GO

For more information, see Server configuration options.

Follow up: After you configure the remote proc trans option

The setting takes effect immediately without restarting the server.

Related content