This operation fails because the ole db Provider sqloledb cannot start distributed transactions)

This operation failed because
Ole db Provider sqloledb cannot start distributed transactions

 

From http://www.cnblogs.com/pato/archive/2010/08/20/1804998.html

Applicable Environment

Operating System:Windows 2003

Database:SQL Server 2000/SQL Server 2005

Remote database access using linked servers

I, Symptom

When executing distributed transactionsSQL Server 2005The following error is returned:

Message7391, Level16, Status2, ProcessXXXXX, No16Line

Unable to perform this operation because the linked server"XXXXX"OfOLE DBAccess interface"Sqlncli"You cannot start distributed transactions.

InSQL
Servers 2000The following error is returned:

This operation failed becauseOLE DBProvideProgram'Sqlodb'You cannot start distributed transactions.

[OLE/DB Provider returned message:
New transactions cannot be registered to the specified transaction processor.]

OLE DBError Tracking [OLE/DB Provider 'sqloledb'
Itransactionjoin: jointransaction returned 0x8004d00a].

II, Solution 1. Both parties start MSDTC Service

MSDTCThe Service provides the distributed transaction service. To use distributed transactions in the database, you must startMSDTC(Distributed
Transaction Coordinator)Service.

2. Open both parties 135 Port

MSDTC the service depends on RPC ( remote
Procedure Call (RPC) ) Service , RPC Use the 135 port, ensure that RPC the service is started, if the server has a firewall, ensure 135 the port is not blocked by the firewall.

 Use"Telnet IP
135"Command to test whether the peer port is open to the outside world. You can also use port scanning software (suchAdvanced Port
Bytes) 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 initiating 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 server that initiates the transaction. Such operations are called loopback (LoopbackIs not supported, so make sure that this operation does not exist in the linked server.

4. Before the transaction starts, add Set
Xact_abort on Statement

For mostOLE DBProvided programs (includingSQL
Server), Must be implicitly or displayed in the transaction data modification statementXact_abortSet
On. The only case where this option is not required is when the Provider supports nested transactions.

5. MSDTC Set

Open "management tools-component services", enable "component services-computer", and right-click "my computer. InMSDTCClick security configuration.

Make the following settings in the Security Configuration window:

LSelect "NetworkDTCAccess"

LIn Client Management, select "allow remote client" and "Allow Remote Management"

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

LGuaranteeDTCLogin Account:NT
Authority \ NetworkService

 

6. Link server and name resolution Problems

Create LinkSQL
ServerServer, usually in two cases:

LIn the first case, select the product"SQL
Server"

Exec sp_addmediaserver

@ Server = 'linkservername ',

@ Srvproduct = n' SQL Server'

In this case,@ Server
(Linkservername) Is the linkSqlserverServer name orIPAddress.

LIn the second case, select the access interface"Microsoft ole db Provider SQL Server"Or"SQL native
Client"

Exec sp_addmediaserver

@ Server = 'linkservername ',

@ Srvproduct = '',

@ Provider = 'sqlncl ',

@ Datasrc = 'sqlservername'

In this case,@ Datasrc(Sqlservername) Is the actual linkSqlserverServer name orIPAddress.

 

SQL ServerThe database engine uses the server name orIPAddress Access link server,DTCThe service only uses the server nameAddress to access the linked server, so ensure that the database engine andDTCYou can use either the server name orIPAddress to access the linked server.

Database Engine andDTCThe method for parsing the server is not the same.

6.1 Database Engine

In the first case@ ServerOr in the second case@ DatasrcSetIPThe Database EngineIPYou do not need to parse the name when accessing the linked server.

In the first case@ ServerOr in the second case@ DatasrcSetSQL
ServerTo resolve a server name, you must resolve the server nameIPAddress.

There are two ways to resolve the server name:

FirstSQL
ServerSet an alias in the client configuration to correspond the preceding server name toIPAddress.

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

XXX. XXXServer Name

The role is to map the server name to the linked serverIPAddress.

6.2 DTC

In either case@ ServerSet the server name insteadIPAddress, you need to resolve the name, the method is the same as above the second method, inHostsAdd resolution records to the file.DTCDoes not work.

If@ ServerSetIPAddress, also do not need to do domain name resolution work.

 

7. Name resolution on Remote Server

The servers that participate in distributed transactions need to access each other. The server that initiates the query depends on the machine name orIPTo find the remote server, the remote server also needs to find the initiator server. The remote server searches for the server by the machine name of the initiator server, therefore, make sure that the remote server can access the initiator server by the machine name of the initiator server.

Generally, the machine names of two servers in the same network segment can be well resolved, but it is not guaranteed that they can be well resolved. Therefore, it is safer:

On the remote server,C: \ windows \ system32 \ drivers \ etc \ hosts"Add a record to the file:

XXX. XXXInitiator server name