SQL Server Distributed Transaction solution [new transactions cannot be registered with the specified transaction processor]

<Doctype HTML public-wcdtd XHTML stricten httpwwwworgtrxhtmldtdxhtml-strictdtd>

Applicable Environment

Operating System: Windows 2003, XP

Databases: SQL Server 2000/SQL Server 2005

Remote database access using linked servers

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.

SQL Server 2000 receives the following error:

This operation cannot be performed because the ole db Provider 'sqlodb' cannot start distributed transactions.

[OLE/DB Provider returned message: a new transaction cannot be registered with a specified transaction processor. ]

Ole db Error Tracking [OLE/DB Provider 'sqloledb' itransactionjoin: jointransaction returned 0x8004d00a].

Ii. solution 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 db providers (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. The DTC service only accesses the linked server through the server name 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.

7. name resolution on the remote server

The servers that participate in distributed transactions need to access each other. The server that initiates the query must search for the remote server based on the machine name or IP address, and the remote server must also search for the initiator server, the remote server searches for the server by the machine name of the initiating server. Therefore, ensure that the remote server can access the initiating server by the machine name of the initiating 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:

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

XXX. XXX launch server name

# Database Technology