SQL分布式事务问题 [英] SQL Distributed Transaction problems

问题描述

亲爱的，我创建一个链接服务器到另一台计算机来创建一个名为yso的同义词，以便从一个表中选择并插入到另一个服务器中的另一个表中，当我尝试执行以下操作时：

  INSERT   INTO  yso （数据）
  SELECT 数据
  FROM  dbo.my 

执行成功但当我用事务执行时（分布式事务）：



< pre lang =sql> BEGIN TRANSACTION
INSERT INTO yso
（数据）
SELECT data
FROM dbo.my
COMMIT 交易

i得到以下错误：

服务器''MyPC''上的MSDTC不可用。

i尝试了很多方法我检查服务分布式事务协调器和分布式链接跟踪客户端在组件服务中，他们正常工作，我重新启动它们，我重新启动数据库服务器，我更改了组件服务/计算机/分布式事务处理协调器下的设置右键单击本地DTC转到安全选项卡并检查所有安全设置并检查是否帐户名称是NT AUTHORITY \NetworkService



所有这些都与我的情况无关，所以我该如何解决这个问题..

解决方案

转到组件服务，在控制台根目录下展开组件服务，然后浏览到计算机 - >我的电脑 - >分布式事务协调器。右键单击本地分布式事务处理协调器，然后单击属性：



转到安全选项卡。首先选中此框以启用网络DTC访问，然后检查所有四个可用的允许选项：

•允许远程客户端

•允许远程管理

•允许入境

•允许出境

此外，您还需要选择无需身份验证并取消选中启用XA交易



然后当您尝试执行您的交易时，您可能会遇到以下错误：

链接服务器的OLE DB提供程序SQLNCLI10 RND-YMAZEH返回消息无法在此会话中启动更多事务。。 
消息7395，级别16，状态2，行23 
无法为链接服务器RND-YMAZEH的OLE DB提供程序SQLNCLI10启动嵌套事务。 
需要嵌套事务，因为XACT_ABORT选项设置为OFF。

所以你只需要取消注释第一行（在开始执行交易之前，您必须添加SET XACT_ABORT ON。

Dears, i create a link server to another computer to create a synonym called "yso" in order to select from one table and insert into another table in another server also when i try to execute the following :

INSERT  INTO yso ( data )
SELECT  data
FROM    dbo.my

execution occurs successfully but when i execute it with transaction (Distributed Transaction):

BEGIN TRANSACTION
INSERT  INTO yso
        ( data )
        SELECT  data
        FROM    dbo.my
COMMIT TRANSACTION

i get the following error:

MSDTC on server ''MyPC'' is unavailable.

i tried many ways "I check the services Distributed transaction Coordinator and Distributed Link Tracking Client in component services and they are working normally also i restart them, i restart the database server, i changed the settings under component Services/computers/Distributed Transaction Coordinator right click on Local DTC go to security tab and check all security settings and check if the account name is NT AUTHORITY\NetworkService"

all these are not working with my case so how can i resolve this issue..

解决方案

Go to component Services, under Console Root expand Component Services and then browse to Computers -> My Computer -> Distributed Transaction Coordinator. Right-click Local Distributed Transaction Coordinator and then click Properties:

Go to security tab. First check the box to enable Network DTC Access and then check all four available Allow options:
• Allow Remote Clients
• Allow Remote Administration
• Allow Inbound
• Allow Outbound
Also you''ll need to select No Authentication Required and uncheck enable XA Transaction

Then when you try to execute your transaction you may face the following error:

OLE DB provider "SQLNCLI10" for linked server "RND-YMAZEH" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 23
Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "RND-YMAZEH".
 A nested transaction was required because the XACT_ABORT option was set to OFF.

So you just need to uncomment the first line (you have to "add SET XACT_ABORT ON") before start executing your transaction.

这篇关于SQL分布式事务问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！