분산 트랜잭션을 시작할 수 없습니다.

분산 트랜잭션을 시작할 수 없습니다.

---

연결된 서버에 대해 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 서버가 서로 통신하도록하려면 어떻게해야합니까?

관련 질문

• OLE DB 공급자“SQLNCLI10”... (* 연결된 서버 이름은 (null)) 이므로 작업을 수행 할 수 없습니다.
• 분산 트랜잭션 오류? ( Oracle 공급자 사용 )
• NHibernate로 분산 트랜잭션에 참여할 수 없음 ( Hibernate 사용 )
• SQL Server 2008 R2에서 분산 트랜잭션 사용 오류 ( SQL Server 2008 R2, 응답 없음 )
• 코드를 통한 분산 트랜잭션 오류 ( 연결 풀링으로 인해 발생 )
• 연결된 서버에서 분산 트랜잭션 조정자를 수행하는 동안 오류 발생 ( SQL Server 2008, 응답 없음 )
• 분산 트랜잭션 오류? ( 수락 된 답변 없음, 답변 만 도움이되지 않음 )
• 트랜잭션 내에서 연결된 서버를 사용하여 원격 테이블에 삽입하는 방법은 무엇입니까? ( 수락 된 답변이 해결되지 않음 )

---

내가 한 일은 관련이 없지만 어쨌든 게시하겠습니다.

1. Distributed Transaction Coordinator두 시스템에서 서비스가 실행 중인지 확인하십시오 .

   

   

2. 두 컴퓨터에서 모든 MSDTC 보안을 비활성화합니다.

   

   

3. 연결된 서버에서 임의 옵션을 켭니다.

4. 저주와 맹세.

5. 박살난 것들.

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.

---

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 ?

---

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

---

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

---

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.

---

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!

---

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

참고URL : https://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction