SQL Server Distributed Transaction Problem _mssql2005

first, the phenomenon of problems

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

Message 7391, Level 16, State 2, process xxxxx, line 16th

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

You receive the following error under SQL Server 2000:

The operation could not be performed because the OLE DB provider ' SQLOLEDB ' could not start a distributed transaction.

[OLE/DB provider returned message: The new transaction cannot be enlisted in the specified transaction processor. ]

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

Ii. Solutions

1. The two sides start the MSDTC service

The MSDTC service provides distributed transaction services, and if you want to use SQL Server distributed transactions in the database, you must start the MSDTC (Distributed Transaction Coordinator) service on both participating servers.

2. Open 135 ports on both sides

The MSDTC service relies on RPC (Remote Procedure call (RPC)) service, RPC uses 135 ports to ensure RPC service starts, and if the server has a firewall, 135 ports are 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 ports to determine whether the ports are open.

3. Ensure that the statements in the linked server do not have access to the originating Transaction Server

The server that initiates the transaction executes a query, view, or stored procedure on the linked server that contains access to the originating Transaction Server, which is called loopback (loopback) and is not supported, so it is guaranteed that no such action exists 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 statements in the implicit or display transaction to ON. The only thing that does not require this option is when the provider supports nested transactions.

5. MSDTC Setup

Open the "Administrative Tools-Component Services" to open "Component Services-Computer" and right-click on "My Computer". In the MSDTC tab, tap the Security Configuration button.

In the Security Configuration window, make the following settings:

L Select Network DTC Access

L Select "Allow remote Client" "Allow remote Administration" in client management

L Select "Allow Inbound" "Allow outbound" in the Transaction Management newsletter "Do not require authentication"

L Guarantee DTC login account as: NT AUTHORITY\NetworkService

6. Linked server and name resolution issues

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

L The first case, the product selected "SQL Server"

EXEC sp_addlinkedserver @server = ' Linkservername ', @srvproduct = N ' SQL Server '
In this case, @server (linkservername) is the SQL Server name or IP address to which you want to link.

L in the second case, the Access interface chooses Microsoft OLE DB Provider sql Server or SQL Native Client

EXEC sp_addlinkedserver @server = ' Linkservername ', @srvproduct = ', @provider = ' SQLNCLI ', @datasrc = ' SQLServerName '
In this case, @datasrc (SQLServerName) is the actual SQL Server name or IP address to which you want to link.

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

The above is a description of the SQL Server Distributed transaction solution, and I hope you can reap the benefits.