无法开始分布式事务 [英] Unable to begin a distributed transaction

问题描述

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

开始分布式交易SELECT TOP 1 * FROM SessionsOLE DB 提供程序SQLNCLI"用于链接服务器ASILIVE"返回消息没有事务处于活动状态.".Msg 7391, Level 16, State 2, Line 3由于 OLE DB 提供程序SQLNCLI"无法执行该操作用于链接服务器ASILIVE"无法开始分布式事务.

提供者返回了两个错误:

错误 #1:

编号:$80040E14来源:Microsoft OLE DB Provider for SQL Server描述:OLE DB 提供程序SQLNCLI"用于链接服务器ASILIVE"返回消息没有事务处于活动状态.".帮助文件:帮助上下文:$00000000SQL 状态:01000本机错误:7412

错误 #2

编号:$80040E14来源:Microsoft OLE DB Provider for SQL Server说明:无法执行该操作，因为 OLE DB 提供程序SQLNCLI"已关闭.用于链接服务器ASILIVE"无法开始分布式事务.帮助文件:帮助上下文:$00000000SQLState:42000本机错误:7391

我如何让 Microsoft 偏爱功能而非安全?

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

相关问题

• 在链接服务器上打开随机选项:

4. 被诅咒和发誓.

5. 砸东西.

6. 检查了 SELECT 可以使用链接服务器:

    SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users....(763 行受影响)

7. 检查客户端服务器可以ping远程服务器:

    C:\Documents and Settings\avatar>ping asicmstest.contoso.com使用 32 字节数据 Ping asicmstest.contoso.com [10.0.0.40]:来自 10.0.0.40 的回复:bytes=32 time<1ms TTL=128来自 10.0.0.40 的回复:bytes=32 time<1ms TTL=128来自 10.0.0.40 的回复:bytes=32 time<1ms TTL=128来自 10.0.0.40 的回复:bytes=32 time<1ms TTL=12810.0.0.40 的 Ping 统计信息:数据包:发送 = 4，接收 = 4，丢失 = 0(0% 丢失)，大约以毫秒为单位的往返时间:最小值 = 0ms，最大值 = 0ms，平均值 = 0ms

8. 检查远程服务器是否可以通过名称与发起服务器通信:

    C:\Documents and Settings\avatar>ping asitestserver.contoso.com使用 32 字节数据 Ping asitestserver.contoso.com [10.0.0.22]:来自 10.0.0.22 的回复:bytes=32 time<1ms TTL=128来自 10.0.0.22 的回复:bytes=32 time<1ms TTL=128来自 10.0.0.22 的回复:bytes=32 time<1ms TTL=128来自 10.0.0.22 的回复:bytes=32 time<1ms TTL=12810.0.0.22 的 Ping 统计信息:数据包:发送 = 4，接收 = 4，丢失 = 0(0% 丢失)，大约以毫秒为单位的往返时间:最小值 = 0ms，最大值 = 0ms，平均值 = 0ms

9. 检查 @@SERVERNAME 是否与两台服务器上的服务器名称匹配:

    SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')------------- -------------ASITESTSERVER ASITESTSERVER

   和

    SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')---------- ----------ASIGROBTEST ASIGROBTEST

10. 尖叫

11. 在发出我的查询之前发出SET XACT_ABORT ON:

    SET XACT_ABORT ON走开始分布式交易SELECT TOP 1 * FROM Sessions

12. 授予 Everyone 完全控制:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

    在两台服务器上.

解决方案

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

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

<块引用>

事件类型:错误
事件来源:MSDTC
活动类别:CM
事件 ID:4101
日期:2011 年 9 月 19 日
时间:下午 1:32:59
用户:不适用
计算机:ASITESTSERVER
说明:

本地 MS DTC 检测到ASICMSTEST 上的 MS DTC 与本地 MS 具有相同的唯一标识故障码.这意味着两个 MS DTC 将无法通信彼此.如果其中一个系统，通常会出现此问题使用不受支持的克隆工具克隆.MS DTC 要求使用受支持的克隆工具(例如 SYSPREP)克隆系统.从命令运行msdtc -uninstall"，然后运行msdtc -install"提示将解决问题.注意:运行msdtc -uninstall"将导致系统丢失所有 MS DTC 配置信息.

有关更多信息，请参阅帮助和支持中心，网址为http://go.microsoft.com/fwlink/events.asp.

运行

msdtc -uninstallmsdtc -安装

然后停止并重新启动 SQL Server 服务修复它.

I'm trying to run SQL against a linked server, but I get the errors below :

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.

There are two errors returned by the provider:

Error #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

Error #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

How do I get Microsoft to favor functionality over security?

Or, at least, how can I get two SQL Severs to talk to each other?

Related questions

• The operation could not be performed because OLE DB provider "SQLNCLI10"... (*linked server name is (null))
• Distributed transaction error? (using Oracle provider)
• Unable to enlist in a distributed transaction with NHibernate (using Hibernate)
• Error using distributed transaction in SQL Server 2008 R2 (SQL Server 2008 R2, no answer)
• Distributed Transaction Error Only Through Code (caused by connection pooling)
• Error performing distributed transaction coordinator in linked server (SQL Server 2008, no answer)
• Distributed transaction error? (no accepted answer; only answer doesn't help)
• How to Insert into remote table using Linked server withint Transaction? (accepted answer doesn't resolve)

---

What I have done is irrelevant, but I'll post it anyway.

1. Ensure Distributed Transaction Coordinator service is running on both machies:

2. Disable all MSDTC security on both machines:

3. Turn on random options on the linked server:

4. Cursed and swore.

5. Smashed things.

6. Checked that a SELECT can use the linked server:

       SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users
       ....
   
       (763 row(s) affected)
   

7. 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
   

8. Checked that the remote server can commnicate back, by name, to the initiating server:

        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
   

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

      SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
   
      -------------  -------------
      ASITESTSERVER  ASITESTSERVER
   

   and

      SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
   
      ----------  ----------
      ASIGROBTEST  ASIGROBTEST
   

10. Screamed

11. Issued SET XACT_ABORT ON before issuing my query:

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

12. Granted Everyone Full Control to:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
    

    on both servers.

解决方案

Found it, MSDTC on the remote server was a clone of the local server.

From the Windows Application Events Log:

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.

Running

msdtc -uninstall
msdtc -install

and then stopping and restarting SQL Server service fixed it.

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