配置远程过程事务服务器配置选项Configure the remote proc trans Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明了如何使用 SQL ServerSQL Server 中配置 SQL Server Management StudioSQL Server Management Studio remote proc trans Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the remote proc trans server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. remote proc trans 选项可通过 MicrosoftMicrosoft 分布式事务处理协调器 (MS DTC) 事务,帮助保护服务器到服务器过程的操作。The remote proc trans option helps protect the actions of a server-to-server procedure through a MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC) transaction.

remote proc trans 的值设置为 1 以提供一个由 MS DTC 协调的分布式事务,该事务保护事务的 ACID(原子、一致、隔离和持久)属性。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. 该选项设置为 1 后开始的会话将继承该配置设置并将其作为默认值。Sessions begun after setting this option to 1 inherit the configuration setting as their default.

重要

下一版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in the next version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

本主题内容In This Topic

开始之前Before You Begin

先决条件Prerequisites

  • 在设定此值前,必须允许远程服务器连接。Remote server connections must be allowed before this value can be set.

建议Recommendations

  • 提供该选项是为了与 MicrosoftMicrosoft SQL ServerSQL Server 早期版本兼容,以支持使用远程存储过程的应用程序。This option is provided for compatibility with earlier versions of MicrosoftMicrosoft SQL ServerSQL Server for applications that use remote stored procedures. 不发出远程存储过程调用,而是使用引用链接服务器的分布式查询,这些服务器是使用 sp_addlinkedserver定义的。Instead of issuing remote stored procedure calls, use distributed queries that reference linked servers, which are defined by using sp_addlinkedserver.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。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. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置 remote proc trans 选项To configure the remote proc trans option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “连接” 节点。Click the Connections node.

  3. “远程服务器连接” 下,选中 “需要将分布式事务用于服务器到服务器的通信” 复选框。Under Remote server connections, select the Require Distributed Transactions for server to server communication check box.

使用 Transact-SQLUsing Transact-SQL

配置 remote proc trans 选项To configure the remote proc trans option

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_configureremote proc trans 选项的值设置为 1This example shows how to use sp_configure to set the value of the remote proc trans option to 1.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'remote proc trans', 1 ;  
GO  
RECONFIGURE ;  
GO  
  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置远程过程事务选项之后Follow Up: After you configure the remote proc trans option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)