This operation cannot be performed because the ole db access interface "sqlncli" of the server "XXXXX" cannot start distributed transactions.

I. symptom
When executing a distributed transaction, you receive the following error under SQL Server 2005:

Message 7391, level 16, status 2, process XXXXX, 16th rows

This operation cannot be performed because the ole db access interface "sqlncli" of the linked server "XXXXX" cannot start distributed transactions.

 

 

Ii. Solutions
1. Both parties start the MSDTC Service
MSDTC provides the distributed transaction service. To use distributed transactions in the database, you must start the MSDTC (Distributed Transaction Coordinator) service on the servers of both parties involved.

2. open port 135 of both parties
MSDTC depends on the Remote Procedure Call (RPC) service. RPC uses port 135 to ensure that the RPC service is started. If the server has a firewall, port 135 is not blocked by the firewall.

Run the "Telnet IP 135" command to test whether the peer port is open to the outside world. You can also use port scanning software (such as advanced port scanning) to scan the port to determine whether the port is open.

3. Ensure that the statements on the linked server do not access the operations on the Transaction Server.
The server that initiates the transaction executes the query, view, or stored procedure on the linked server to access the operations on the initiating Transaction Server. Such operations are called loopback and are not supported, therefore, make sure that this operation does not exist on the linked server.

4. Add the set xact_abort on statement before the transaction starts.
For most Ole databasesProgram(Including SQL Server), xact_abort In the implicit or display transaction data modification statement must be set to on. The only case where this option is not required is when the Provider supports nested transactions.

5. MSDTC settings
Open "management tools-component services", enable "component services-computer", and right-click "my computer. On the MSDTC tab, click "Security Configuration.

Make the following settings in the Security Configuration window:

L select "Network DTC Access"

L select "allow remote client" and "Allow Remote Management" in Client Management"

L select "Allow inbound" "Allow outbound" in transaction management communications, and do not require verification"

L ensure that the DTC login account is: nt authority \ NetworkService

6. Link server and name resolution Problems
There are two common scenarios for creating a link to an SQL Server:

L in the first case, select "SQL Server" for the product"

Exec sp_addmediaserver

@ Server = 'linkservername ',

@ Srvproduct = n' SQL Server'

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

L in the second case, select "Microsoft ole db Provider SQL Server" or "SQL native client" as the access interface"

Exec sp_addmediaserver

@ Server = 'linkservername ',

@ Srvproduct = '',

@ Provider = 'sqlncl ',

@ Datasrc = 'sqlservername'

In this case, @ datasrc (sqlservername) is the actual sqlserver server name or IP address to be linked.

The SQL Server database engine accesses the linked server through the server name or IP address set above, and the DTC service also accesses the linked server through the server name or IP address, therefore, make sure that both the database engine and DTC can access the linked server through the server name or IP address.

The methods used by the database engine and the DTC parsing server are not the same.

6.1 Database Engine
In the first case, @ server, or in the second case, when @ datasrc is set to an IP address, the database engine accesses the linked server based on the IP address, and no name resolution is required.

In the first case, when @ server or in the second case @ datasrc is set to the name of the SQL Server server, you need to resolve the name to the IP address.

There are two ways to resolve the server name:

First, set an alias in the SQL Server Client configuration to correspond the above server name to the IP address of the linked server.

Second, add a record to the "C: \ windows \ system32 \ drivers \ etc \ hosts" file:

XXX. XXX server name

It also maps the server name to the IP address of the linked server.

6.2 DTC
In either case, as long as @ server is set to the server name rather than the IP address, name resolution is required. The method is the same as the method above. Add resolution records in the hosts file, the first method above does not work for DTC.

If @ server is configured with an IP address, domain name resolution is not required.