logo标签列表

无法开始分布式事务

sql-serversql-server-2005msdtcdistributed-transactions
106
我试图对一个链接服务器运行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.

提供者返回了两个错误：
第一个错误：
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

如何让微软更加注重功能而非安全性？
或者，我该如何让两个SQL服务器之间进行通信？
相关问题。
我所做的是无关紧要的，但我会发帖。

  1. 确保两台计算机上运行Distributed Transaction Coordinator服务：

    enter image description here

    enter image description here

  2. 在两台计算机上禁用所有MSDTC安全选项：

    enter image description here

    enter image description here

  3. 打开链接服务器上的随机选项：

enter image description here

  1. Cursed and swore.

  2. Smashed things.

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

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

    (763 row(s) affected)

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

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

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

       SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

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

    and

       SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

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

  7. Screamed

  8. Issued SET XACT_ABORT ON before issuing my query:

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

  9. Granted Everyone Full Control to:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

    on both servers.

39
很高兴看到你没有跳过关键的“诅咒和咒骂”的步骤！ - jwl
12
嗨，有时候人们会非常在意你是否遵循了每一个步骤。我不想让任何人说我没有尝试过某件事情。 - Ian Boyd
7
我喜欢这篇文章。它典型地体现了我与 MSDTC 的所有经历。 - A. Murray
5
在我的情况下，当我将链接服务器上的“启用RPC分布式事务促进”设置为False时，它起作用了。也许你可以尝试一下。 - Sarsaparilla
4
关闭分布式事务的使用会破坏跨节点事务的完整性：如果在本地服务器上回滚更改，意味着远程服务器上的更改将保持不变。非常危险。 - Ian Boyd
11个回答
0
昨天我遇到了一个类似的问题，所以我想分享一下我的经验和解决方案。
我们试图使用“链接服务器”将源表中的行插入到目标表中，但是我们遇到了错误提示“无法开始分布式事务”。
尝试了多种方法后，我们发现问题出在我们直接在同义词中使用了目标表的名称。
最终，我们在目标服务器上创建了一个存储过程来插入行，并且从我们的源同义词中调用该存储过程，结果一切都顺利解决了。希望对你有所帮助。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接