SQL Server DT Ask and Answer

Server
Q: Suppose I now have 2 SQL Server (Windows Server 2003 + SQL Server SP3), named DB01 and DB02, trusting each other, adding each other to the list of linked servers, without any problems with regular access, but Unable to enable distributed transactions, examples are as follows:

Run the following SQL command on the DB01:

BEGIN Tran

Select top 1 * from item

Select top 1 * from Db02.production.dbo.item

Rollback Tran

At the 2nd SELECT statement, the operation could is performed because the OLE DB provider ' SQLOLEDB ' is unable to begin a distribut Ed transaction.
[OLE/DB provider returned MESSAGE:NEW transaction cannot enlist in the specified transaction.]
OLE DB error trace [ole/db Provider ' SQLOLEDB ' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Replace the BEGIN TRAN with the begin distributed TRANSACTION, and the results are the same.

On these 2 servers, the MS DTC service has been enabled at the same time and the various security support options for MS DTC have been set in the component service (steps below:

1. Click Start Programs, point to Administrative Tools, and click Component Services.
2. In the Component Services console, expand Component Services, expand Computers, right-click My Computer, and then click Properties.
3. In the I Computer Properties dialog box, on the MSDTC tab, click Security Configuration.
4. In the Security Configuration dialog box, select the Network DTC Access check box, select the Network administration CH Eck box, select the Network Transactions check box, select the Network Clients check box, select the Transaction Internet Protocol (TIP) Transaction check box, and then click OK.
5. Restart the computer.
）。
Because the server is running the task, I did not reboot (only the DTC service was restarted).
What causes the transaction to not start?

A:

I. (missing)

Two. Is the MSDTC of two machines open?

Three. MSDTC settings are correct.
1. Open the command prompt, run "net stop msdtc" and run "net start MSDTC".
2. Go to the Component Services administration tool.
3. Browse to the Start management tool.
4. Select "Component Services".
A. Expand the Component Services tree, and then expand My Computer.
B. Right-click My Computer, and then select Properties.
C. In the MSDTC tab, make sure that the following options are selected: Network DTC access
Network management
Network transactions
XA transactions
E. In addition, the "DTC login account" must be set to "NT AUTHORITY\NetworkService".
5. Click OK. This will prompt you to "MS DTC will stop and reboot."
All dependent services will be stopped. Please press ' yes ' to ' continue '. Click "Yes" to continue.
6. Click OK to close the My Computer Properties window.

Four.
MSDTC relies on the port used by RPC,RPC to be 135 and test that port 135 is open. Is there a firewall? If there is a firewall turned off first.
Telnet IP 135
If it is turned off, open it.

Five.
For a variety of reasons, SQLOLEDB cannot use distributed transactions and change to "MSDASQL" ODBC-mode joins.
Use RRAS instead of RAS. (Control surface--Administrative tools--remote service manager)
Check Whether you are the using remote Access Server (RAS) to Access remote servers. If So, make sure this you have implemented Routing RAS (RRAS). Linked server does not work on RAS because RAS allows only one way communication.


Seven. Check to see if your two servers are in the same domain.
Whether to establish a trusted join if it is not in the same domain.

Eight. If it is WIN2000, upgrade to SP4

Nine. Upgrade MDAC to more than 2.6, preferably 2.8.

10. The latest patch to install SQL: SP3a
' Location of all patches
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID= 9032f608-160a-4537-a2b6-4cb265b80766

' Should install the patch SQL SP3
Http://download.microsoft.com/download/d/d/e/dde427eb-0296-4eac-a47c-d11a66b03816/chs_sql2ksp3.exe



I think:
1, the environment is important.

2, the tool is very important.
(1) Suspect is 135 port, use a check port tool to see, found that the port is not a problem.
(2) under the Http://download.microsoft.com/download/complus/msdtc/1.7/nt45/en-us/DTCPing.exe
Tools, tests, looked at the readme, said by three steps, the first step verifies the name, the second verifies the RPC, the third step DTC, well, I actually found out that the name verification didn't pass,
Always find the NetBIOS of TCP/IP is turned off, cough DTC incredibly must name. No IP. I served him.

3, the remote computer can only use IP address access can not be accessed by name, I came across as follows.
(1), routing problems, whether the two routes are correct (VPN connectivity does not mean that name access can be successful, consistent version, allowing the Fang Pro access, no other exceptions).
(2), if error log DCOM occurs, this error will not be accessed by name.
(3), the appropriate TCP/IP NetBIOS is turned on.