The operation could not be performed because the OLE DB provider "SQLNCLI" of the linked server "xxxxx" could not start the distributed transaction

A transaction is used in a stored procedure, and a linked server is used to report an error similar to the following

The OLE DB provider "SQLNCLI10" of the linked server "* * *" returned the message "There is no active transaction." "。
Msg 7391, Level 16, State 2, procedure Proc_syncdiliverydata, line 20th
The operation could not be performed because the OLE DB provider "SQLNCLI10" of the linked server "*****r" could not start the distributed transaction.

degrees Niang for a long time, found two more comprehensive treatment methods, in theory, according to these two methods step-in-place problem can be solved, but I did not solve the ...This article was later found:

https://blogs.msdn.microsoft.com/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/

Download dtcping Tools tool, only to find the port problem (Manual Check not checked in place, not the system fire Wall, the original is a separate installation of a fireproof wall ), so if according to method one, Method two after processing has not been resolved, use this tool to verify the problem!

Method One:

Workaround:

1. Check the MSDTC settings, which are available on the Internet, not many instructions. You can refer to method two

2. Modify the host settings and, under C:\WINDOWS\SYSTEM32\DRIVERS\ETC, add the IP address and computer name of the computer to each of the two servers, as follows:

Peer computer IP Offset computer name

-------------------------------------------------

192.168.88.52 vwin2k3-8852

3. Open both servers, then open SSMs, right-click the database server properties, check "need to use distributed transactions for server-to-server communication" in connection options.

Restart both database services, done.

Method 2

First, problem phenomenon

When you execute a distributed transaction, you receive the following error under SQL Server 2005:

Msg 7391, Level 16, State 2, procedure xxxxx, line 16th

The operation could not be performed because the OLE DB provider "SQLNCLI" of the linked server "xxxxx" could not start the distributed transaction.

You receive the following error under SQL Server 2000:

The operation failed to execute because the OLE DB provider ' SQLOLEDB ' could not start the distributed transaction.

[OLE/DB provider returned message: The new transaction cannot enlist into the specified transaction processor. ]

OLE DB Error tracing [ole/db Provider ' SQLOLEDB ' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Second. Solution

 1. Both sides start the MSDTC service

The MSDTC service provides a distributed transaction service that, if you want to use distributed transactions in a database, you must start the MSDTC (Distributed Transaction Coordinator) service on both sides of the participating servers.

2. Open both sides 135 ports

The MSDTC service relies on the RPC (Remote Procedure call (RPC)) service, RPC uses port 135 to ensure that the RPC service starts, and if the server has a firewall, ensure that port 135 is not blocked by the fire wall.

Use the Telnet IP 135 command to test whether the other port is open to the outside. Port scanning software (such as advanced Port Scanner) can also be used to scan the port to determine if the port is open.

3. Ensure that the statement in the linked server does not have access to the initiating Transaction Server

In a query, view, or stored procedure on a linked server where the server initiating the transaction contains access to the initiating transaction Server, such operations are called loopback (loopback) and are not supported, so ensure that no such operations exist on the linked server.

4. Add the SET XACT_ABORT on statement before the transaction begins

For most OLE DB providers, including SQL Server, you must set the Xact_abort in the data modification statement in the implicit or display transaction to ON. The only case where this option is not required is when the provider supports nested transactions.

5. MSDTC Settings

Open "Component Services-Computer" by opening "Administrative Tools-Component Services" and right-click on "My Computer". On the MSDTC tab, click the "Security Configuration" button.

In the Security Configuration window, make the following settings:

• Select "Network DTC Access"

• In client management, select Allow remote clients to allow remote administration

L Select "Allow Inbound" "Allow Outbound" "Do not require authentication" in transaction management communication

L Ensure DTC Login account is: NT AUTHORITY\NetworkService

6. Linked server and name resolution issues

There are two common scenarios for establishing a linked SQL Server server:

L first case, product select "SQL Server"

EXEC sp_addlinkedserver

@server = ' Linkservername ',

@srvproduct = N ' sql Server '

In this case, the @server (linkservername) is the SQL Server name or IP address to be linked.

In the second case, Access interface selected "Microsoft OLE DB Provider sql Server" or "SQL Native Client"

EXEC sp_addlinkedserver

@server = ' Linkservername ',

@srvproduct = ' ',

@provider = ' SQLNCLI ',

@datasrc = ' SQLServerName '

In this case, the @datasrc (SQLServerName) is the actual SQL Server name or IP address to link to.

The SQL Server database engine accesses the linked server through the server name or IP address set above, and the DTC service accesses the linked server only through the server name address, so ensure that both the database engine and the DTC can access the linked server through the server name or IP address.

The database engine and the DTC parsing server are not the same way, the following respectively describes

6.1 Database Engine

When the first case of @server or the second case @datasrc is set to an IP address, the database engine accesses the linked server based on the IP address, which does not require name resolution.

The first case of @server or the second case of @datasrc is set to the SQL Server server name, you need to do name resolution, that is, to resolve the server name to an IP address.

There are two ways to parse the server name:

One is to set an alias in the SQL Server client configuration that corresponds to the server name above to the IP address of the linked server.

The second is to add a record in the "C:\WINDOWS\system32\drivers\etc\hosts" file:

xxx.xxx.xxx.xxx Server Name

The function is also the IP address that corresponds to the server name to the linked server.

6.2 DTC

In either case, as long as the @server set the server name instead of the IP address, you need to do name resolution, the method with the second method above, in the Hosts file to add parse records, the first method above the DTC does not work.

If the @server is set to an IP address, the same does not need to do domain name resolution work.

7. Name resolution on a remote server

The participating servers of the distributed transaction need mutual access, the server that initiates the query is looking for the remote server according to the machine name or IP, and the remote server also finds the originating server, and the remote server locates the server by the name of the originating server. So make sure that the remote server can access the originating server by initiating the machine name of the server.

In general, two servers in the same network segment machine name can be good parsing, but also does not guarantee that all can be very good analysis, so the more insurance approach is:

In the remote server, add a record in the "C:\WINDOWS\system32\drivers\etc\hosts" file:

xxx.xxx.xxx.xxx initiating the server name

——

The operation could not be performed because the OLE DB provider "SQLNCLI" of the linked server "xxxxx" could not start the distributed transaction