OLE DB Provider "SQLNCLI" cannot start a distributed transaction without an active transaction linked server

The following occurs when performing a distributed transaction under WINDOWS2003.

First, the problem phenomenon
When you execute a distributed transaction, you receive the following error under SQL Server 2005:

The OLE DB provider "SQLNCLI" of the linked server "xxxxxxx" returned the message "There is no active transaction." "。

Msg 7391, Level 16, State 2, procedure xxxxx, line 16th

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

You receive the following error under SQL Server 2000:

The operation failed to execute because the OLE DB provider ' SQLOLEDB ' could not start the distributed transaction.

[OLE/DB provider returned message: The new transaction cannot enlist into the specified transaction processor. ]

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

Second, the solution------- using dctping tool detection
1. Both sides start the MSDTC service-DTC, RPC service
The MSDTC service provides a distributed transaction service that, if you want to use distributed transactions in a database, you must start the MSDTC (Distributed Transaction Coordinator) service on both sides of the participating servers.

2. Open both sides 135 ports (or turn off the firewall )
The MSDTC service relies on the RPC (Remote Procedure call (RPC)) service, RPC uses port 135 to ensure that the RPC service starts, and if the server has a firewall, ensure that port 135 is 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 the port to determine if the port is open.

3. Ensure that the statement in the linked server does not have access to the initiating Transaction Server
In a query, view, or stored procedure on a linked server where the server initiating the transaction contains access to the initiating transaction Server, such operations are called loopback (loopback) and are not supported, so ensure that no such operations exist 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 statement in the implicit or display transaction to ON. The only case where this option is not required is when the provider supports nested transactions.

5. MSDTC Settings
Open "Component Services-Computer" by opening "Administrative Tools-Component Services" and right-click on "My Computer". On the MSDTC tab, click the "Security Configuration" button.

In the Security Configuration window, make the following settings:

• Select "Network DTC Access"

• In client management, select Allow remote clients to allow remote administration

L Select "Allow Inbound" "Allow Outbound" "Do not require authentication" in transaction management communication

L Ensure DTC Login account is: NT AUTHORITY\NetworkService

6. Linked server and name resolution issues
There are two common scenarios for establishing a linked SQL Server server:

L first case, product select "SQL Server"

EXEC sp_addlinkedserver

@server = ' Linkservername ',

@srvproduct = N ' sql Server '

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

In the second case, Access interface selected "Microsoft OLE DB Provider sql Server" or "SQL Native Client"

EXEC sp_addlinkedserver

@server = ' Linkservername ',

@srvproduct = ' ',

@provider = ' SQLNCLI ',

@datasrc = ' SQLServerName '

In this case, the @datasrc (SQLServerName) is the actual SQL Server name or IP address to link to.

The SQL Server database engine accesses the 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 ensure that both the database engine and the DTC can access the linked server through the server name or IP address.

The database engine and the DTC parsing server are not the same way, the following respectively describes

6.1 Database Engine
When the first case of @server or the second case @datasrc is set to an IP address, the database engine accesses the linked server based on the IP address, which does not require name resolution.

The first case of @server or the second case of @datasrc is set to the SQL Server server name, you need to do name resolution, that is, to resolve the server name to an IP address.

There are two ways to parse the server name:

One is to set an alias in the SQL Server client configuration that corresponds to the server name above to the IP address of the linked server.

The second is to add a record in the "C:\WINDOWS\system32\drivers\etc\hosts" file:

xxx.xxx.xxx.xxx Server Name

The function is also the IP address that corresponds to the server name to the linked server.

6.2 DTC
In either case, as long as the @server set the server name instead of the IP address, you need to do name resolution, the method with the second method above, in the Hosts file to add parse Records , the first method above the DTC does not work.

If the @server is set to an IP address, the same does not need to do domain name resolution work.

=======================================================================

The above is I go from the Internet, in fact, in the setting of the MSDTC Security section, allow remote clients this can not be ticked, but the most important is the next step, in the transaction management communication to select "Allow Inbound" "Allow Outbound" "Do not require authentication", In windows2003 the default is the first choice to verify the two sides, I encountered the situation, the changes will not be an error . In addition, the following login user can only use the network service, and other in SQL Server will say that MSDTC is not available.

=======================================================================

++++++++++++++++++++++++ "The following is personal content" ++++++++++++++++++++++++++++++++++++

=======================================================================

The article turns from the network, http://sai5d.blog.163.com/blog/static/62225483200901322939411/ above color annotation text is the place needing attention, other general default is OK, Overall configuration of triggers between different servers MSDTC needs to pay attention to three aspects , the above server2003 is one, the following also has two: host parsing, MSDTC denied access-------These are first used dtcping Tool Test Connectivity

Note : The hosts resolution can not be immediately effective issue and article title This problem is generally appearing on the server2003, on the server2008 is very rare, but MSDTC denied access to the problem is Win7, server2008 common.

Additionally, the workaround for Windows Server 2003 "Hosts file changes does not take effect" is attached:

Open the Properties page for the locally connected Internet (TCP/IP) protocol, click the Advanced button in the General tab, select the WINS tab, tick "Enable LMHOSTS query", and then press the "Import LMHOSTS" button to import the c:/windows/system32/ Drivers/etc under the Hosts file, so that when you start the browser again, you will find that the site is blocked, the hosts file takes effect.

--Transfer from http://blog.csdn.net/jaycxing/article/details/5286150

　　Workaround for "MSDTC denied access" :

1. Click Start, click Run, type Gpedit.msc, and then click OK to open Group Policy.
2. After you open Group Policy, select the limit for unauthenticated RPC clients, system-by-remote procedure call, Administrative Templates, computer configuration, and so on.
3. Double-click the "limit for unauthenticated RPC clients" and select "Properties" in the popup context menu.
4. In the Properties dialog box that pops up, select the Settings property page, select enabled, client restrictions to apply RPC Runtime unauthenticated, and select None.
5. Click OK to complete the setup.

---transferred from http://blog.csdn.net/xvhaijun/article/details/4505461

OLE DB Provider "SQLNCLI" cannot start a distributed transaction without an active transaction linked server