sql-server - 无法开始分布式事务

Question

我尝试对链接服务器运行 SQL，但出现以下错误:

BEGIN DISTRIBUTED TRANSACTION
SELECT TOP 1 * FROM Sessions


OLE DB provider "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" was unable to begin a distributed transaction.

提供者返回两个错误:

错误＃1:

Number: $80040E14
Source: Microsoft OLE DB Provider for SQL Server
Description: OLE DB provider "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.".
HelpFile: 
HelpContext: $00000000
SQLState: 01000
NativeError: 7412

错误#2

Number: $80040E14
Source: Microsoft OLE DB Provider for SQL Server
Description: The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" was unable to begin a distributed transaction.
HelpFile: 
HelpContext: $00000000
SQLState: 42000
NativeError: 7391

如何让 Microsoft 更看重功能而不是安全？

或者，至少，我怎样才能让两个 SQL Server 相互通信？

相关问题

• The operation could not be performed because OLE DB provider “SQLNCLI10”... (*链接服务器名称为(null))
• Distributed transaction error? (使用 Oracle 提供程序)
• Unable to enlist in a distributed transaction with NHibernate (使用 Hibernate)
• Error using distributed transaction in SQL Server 2008 R2 (SQL Server 2008 R2，没有答案)
• Distributed Transaction Error Only Through Code (由连接池引起)
• Error performing distributed transaction coordinator in linked server (SQL Server 2008，没有答案)
• Distributed transaction error? (没有接受的答案；只有答案没有帮助)
• How to Insert into remote table using Linked server withint Transaction? (接受的答案无法解决)

<小时/>

我所做的事情无关紧要，但无论如何我都会发布。

1. 确保分布式事务协调器服务在两台机器上运行:

   

   

2. 禁用两台计算机上的所有 MSDTC 安全性:

   

   

3. 在链接服务器上启用随机选项:

咒骂发誓。

砸碎东西。

检查 SELECT 是否可以使用链接服务器:

    SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users
    ....

    (763 row(s) affected)

Checked that client server can ping the remote server :

     C:\Documents and Settings\avatar>ping asicmstest.contoso.com

     Pinging asicmstest.contoso.com [10.0.0.40] with 32 bytes of data:

     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128

     Ping statistics for 10.0.0.40:
         Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
     Approximate round trip times in milli-seconds:
         Minimum = 0ms, Maximum = 0ms, Average = 0ms

检查远程服务器是否可以按名称与发起服务器进行通信:

     C:\Documents and Settings\avatar>ping asitestserver.contoso.com

     Pinging asitestserver.contoso.com [10.0.0.22] with 32 bytes of data:

     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128

     Ping statistics for 10.0.0.22:
         Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
     Approximate round trip times in milli-seconds:
         Minimum = 0ms, Maximum = 0ms, Average = 0ms

Checked that @@SERVERNAME matches the server name on both servers :

   SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

   -------------  -------------
   ASITESTSERVER  ASITESTSERVER

和

   SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

   ----------  ----------
   ASIGROBTEST  ASIGROBTEST

尖叫

Issued SET XACT_ABORT ON before issuing my query :

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT TOP 1 * FROM Sessions

Granted Everyone Full Control to :

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

在两台服务器上。

Answer 1

发现，远程服务器上的MSDTC是本地服务器的克隆。

来自 Windows 应用程序事件日志:

Event Type: Error
Event Source: MSDTC
Event Category: CM
Event ID: 4101
Date: 9/19/2011
Time: 1:32:59 PM
User: N/A
Computer: ASITESTSERVER
Description:

The local MS DTC detected that the MS DTC on ASICMSTEST has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

正在运行

msdtc -uninstall
msdtc -install

然后停止并重新启动 SQL Server 服务即可修复该问题。