SQL Server distributed transactions

I. symptom

When executing SQL Server distributed transactions, SQL Server 2005 receives the following error:

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. Solutions

1. Both parties start the MSDTC Service

MSDTC provides the Distributed Transaction service. To use SQL Server Distributed transactions in a 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, 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 above content is an introduction to the SQL Server Distributed Transaction solution, and I hope you will get something.