MS SQL 错误:The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "test" was unable to begin a distributed transaction.
2014-03-05 11:39 潇湘隐者 阅读(7373) 评论(0) 编辑 收藏 举报一同事在测试服务器(系统:Windows 2008 R2 Standard 数据库:SQL SERVER 2008 R2)通过链接服务器test使用分布式事务测试时出错,出错信息如下:
set xact_abort on
begin tran
update test.mydb.dbo.test_one set name='test' where id= 3 ;
commit
OLE DB provider "SQLNCLI10" for linked server "test" returned message "The transaction manager has disabled its support for remote/network transactions.".
消息 7391,级别 16,状态 2,第 5 行
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "test" was unable to begin a distributed transaction.
出现这个错误时因为这台服务器,或者链接服务器那台服务器没有配置好MS DTC(Microsoft Distributed Transaction Coordinator 微软 分布式事务协调器)。
首先确保开启了 'remote access'、 'remote admin connections'、 'remote proc trans'选项。
- exec sp_configure 'show advanced options',1;
- go
- reconfigure;
- go
- exec sp_configure 'remote access',1;
- go
- reconfigure;
- go
- exec sp_configure 'remote admin connections',1;
- go
- reconfigure;
- go
- exec sp_configure 'remote proc trans',1 ;
- go
- reconfigure;
- go
配置DTC步骤
Step 1:Start(开始)-Control Panel(控制面板)-System and Security(系统和安全)- Administrative Tools(管理工具)-Component Services(组件服务)(或者Run->dcomcnfg 直接跳出组件服务)。
进入界面点击(Console Root->Componet Services->Computers->My Computer-> Local DTC)
Step 2: 右键单击"Local DTC"的属性,就可以看到“Tracing”、“Logging”、“Security”三个面板。关于设置属性。可以点击下面的“Learn more about setting these properties”
如下所示,一般只用设置“Security”下的属性,具体情况,根据实际情况配置,可以参考下面“本地DTC属性页:“安全选项”卡说明
注意:如果服务器开启了防火墙,那么必须确保防火墙是否开放了135端口(即允许Distributed Transaction Coordinator程序通过防火墙)
参考资料:
![](https://images.cnblogs.com/cnblogs_com/kerrycode/1913302/o_240731062102_kerrycode.png)