无法启动分布式事务

我试图在一个链接服务器上运行 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

我如何让微软偏爱功能而不是安全？

或者，至少，我如何让两个 SQLSever 相互通信？

相关问题

我所做的 有是无关紧要的，但我会张贴它无论如何。

  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. 被诅咒，被诅咒。

  2. 砸碎的东西。

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

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


(763 row(s) affected)

  4. 检查客户端服务器是否可以 ping远程服务器 :

         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. 检查远程服务器是否可以通过名称与发起服务器通信:

         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. 检查 @@SERVERNAME是否与两台服务器上的服务器名称匹配 :

       SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')


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

    还有

       SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')


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

  7. 尖叫

  8. 在发出查询 之前发出 SET XACT_ABORT ON:

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

  9. 授予 Everyone Full Control至 :

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

    在两个服务器上。

204833 次浏览
小开

OK, so services are started, there is an ethernet path between them, name resolution works, linked servers work, and you disabled transaction authentication.

My gut says firewall issue, but a few things come to mind...

  1. Are the machines in the same domain? (yeah, shouldn't matter with disabled authentication)
  2. Are firewalls running on the the machines? DTC can be a bit of pain for firewalls as it uses a range of ports, see http://support.microsoft.com/kb/306843 For the time being, I would disable firewalls for the sake of identifying the problem
  3. What does DTC ping say? http://www.microsoft.com/download/en/details.aspx?id=2868
  4. What account is the SQL Service running as ?
小开
最佳答案

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.

小开

My last adventure with MSDTC and this error today turned out to be a DNS issue. You're on the right track asking if the machines are on the same domain, EBarr. Terrific list for this issue, by the way!

My situation: I needed a server in a child domain to be able to run distributed transactions against a server in the parent domain through a firewall. I've used linked servers quite a bit over the years, so I had all the usual settings in SQL for a linked server and in MSDTC that Ian documented so nicely above. I set up MSDTC with a range of TCP ports (5000-5200) to use on both servers, and arranged for a firewall hole between the boxes for ports 1433 and 5000-5200. That should have worked. The linked server tested OK and I could query the remote SQL server via the linked server nicely, but I couldn't get it to allow a distributed transaction. I could even see a connection on the QA server from the DEV server, but something wasn't making the trip back.

I could PING the DEV server from QA using a FQDN like: PING DEVSQL.dev.domain.com

I could not PING the DEV server with just the machine name: PING DEVSQL

The DEVSQL server was supposed to be a member of both domains, but the name wasn't resolving in the parent domain's DNS... something had happened to the machine account for DEVSQL in the parent domain. Once we added DEVSQL to the DNS for the parent domain, and "PING DEVSQL" worked from the remote QA server, this issue was resolved for us.

I hope this helps!

小开

If the servers are clustered and there is a clustered DTC you have to disable security on the clustered DTC not the local DTC.

小开

Apart from the security settings, I had to open some ports on both servers for the transaction to run. I had to open port 59640 but according to the following suggestion, port 135 has to be open. http://support.microsoft.com/kb/839279

小开

If your Destination server is on another cloud or data-center then need to add host-entry of MSDTC service(Destination Server) in your source server.

Try this one if problem doesn't resolved, After enable the MSDTC settings.

小开

I was able to resolve this issue (as others mentioned in comments) by disabling "Enable Promotion of Distributed Transactions for RPC" (i.e. setting it to False):

enter image description here

As requested by @WonderWorker, you can do this via SQL script:

EXEC master.dbo.sp_serveroption
@server = N'[mylinkedserver]',
@optname = N'remote proc transaction promotion',
@optvalue = N'false'
小开

I was getting the same error and i managed to solve it by configuring the MSDTC properly on the source server to allow outbound and allowed the DTC through the windows firewall.

Allow the Distributed Transaction Coordinator, tick domain , private and public options

小开

For me, it relate to Firewall setting. Go to your firewall setting, allow DTC Service and it worked.enter image description here

小开

The Firewall settings to Allow the DTC the ticking Private and Public as well as Domain did the trick. Just Domain is not enough (it feels like it should be, but nah).


提交答案