SQL distributed transactions were finally completed after a depressing Study for nearly ten days. The environment win2k3 + SQL2000/sql2005
All questions are summarized as follows (including Summary of online materials)
1. Question 1
This operation cannot be performed because the ole db Provider 'sqlodb' cannot start distributed transactions.
Ole db Error Tracking [OLE/DB Provider 'sqloledb' itransactionjoin: jointransaction returned 0x8004d01c].
Solution:
-- Start the MSDTC Service of the remote server
Exec master .. xp_expose shell 'isql/s "192.168.0.34"/u "DL"/P "333263"/Q "Exec master .. xp_cmdshell ''net start MSDTC '', no_output" ', no_output -- start the MSDTC Service of the local machine.
Exec master.. xp_mongoshell 'net start MSDTC ', no_output 2, question 2
Error message:
[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].
This operation cannot be performed because the ole db Provider 'sqlodb' cannot start distributed transactions. Solution: because one server does not support 'sqlodb', the statement is executed on this server. Debugging method:
I. a. Do not use transactions. Use select statements. Can I perform distributed queries?
B. linkserver cannot operate on the local machine when performing distributed update transactions (that is, it cannot return distributed transactions)
C. DBCC traceon (3604,730 0) -- use tracking to view more detailed error information.
D.download the dtcping.exe package of msbench on two machines and run it according to readme instructions. See error information.
Http://download.microsoft.com/download/complus/msdtc/1.7/nt45/en-us/DTCPing.exe
2. Whether MSDTC is enabled for both machines.
Iii. Whether MSDTC is set correctly.
1. Open the command prompt, Run "net stop MSDTC", and then run "Net start MSDTC ".
2. Go to "Component Service Management Tools ".
3. Browse to "start management tool ".
4. Select "component service ".
A. Expand the "component service" tree and then expand "my computer ".
B. Right-click "my computer" and select "properties ".
C. On the MSDTC tab, make sure the following options are selected: Network DTC Access
Network Management
Network transactions
XA transaction
E. In addition, "DTC Logon account" must be set to "nt authority/NetworkService ".
5. Click OK ". This will prompt you that "Ms DTC will stop and restart.
All dependent services will be stopped. Press "yes" to continue ". Click "yes" to continue.
6. Click "OK" to close the "my computer" Properties window.
4.
MSDTC depends on rpc. RPC uses port 135 to test whether port 135 is enabled. Is there a firewall? If the firewall is disabled first.
Telnet IP 135
If it is disabled, open it.
5.
For various reasons, sqloledb cannot use distributed transactions and is changed to "msdasql" ODBC connection.
Use RRAS instead of RAS. (control panel -- Management Tools -- remote service manager)
Check whether you are using remote access server (RAS) to access remote servers. if so, make sure that you have implemented routing RAS (RRAS ). linked server does not work on Ras because Ras allows only one way communication.
7. Check whether your two servers are in the same domain.
Whether to establish a trusted connection if it is not in the same domain.
8. Upgrade to SP4.
9. Upgrade MDAC to 2.6 or above, preferably 2.8.
10. Install the latest SQL patch: sp3a
'Location Of All Patches
Http://www.microsoft.com/downloads/details.aspx? Displaylang = ZH-CN & familyid = 9032f608-160a-4537-a2b6-4cb265b80766
'The patch to be installed SQL 2000 sp3
Http://download.microsoft.com/download/d/d/e/dde427eb-0296-4eac-a47c-d11a66b03816/chs_sql2ksp3.exe
If the problem persists, continue with the following operations:* *** Make sure that port 135 is enabled on the firewall and MSDTC is allowed. run the EXE program. If Windows has a built-in firewall, add the exception program: C:/Windows/system32/msdtc.exe *****. Check whether the NetBIOS protocol is installed, you can also confirm that the two machines can use the computer name instead of the IP address. If the ping succeeds, add a record in 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.
* *** Finally, check the registry HKEY_LOCAL_MACHINE/software/Microsoft/MSDTC and confirm the settings as follows:
Allowonlysecurerpccils = 0
Turnoffrpcsecurity = 1
Because the security settings of win2k3 are high, the Registry may be changed by default or after the latest security patch is installed. Therefore, if an error occurs during running, this should be the first check item.
