SQL Server 2000 Distributed Transaction Error resolution

Source: Internet
Author: User
Tags continue ole require server error log

If you run a linked server query in the SQLServer2000 installed on Windows.netserver, the following error may be returned (even if MSDTC is running on the server):

Symptom if you run a linked server query in the SQLServer2000 installed on Windows.netserver, the following error may be returned (even if MSDTC is running on the server): Server:msg8501,level16,state3,line3 Msdtconserver ' servername ' Isunavailable. In the SQL Server error log, you will find the following errors raised by the SPID that runs the query: resourcemanagercreationfailed:resultcode= 0x8004d01c reason this is the design dictates. This error occurs if the Distributed Transaction Coordinator service is running under the local System account. Solution Recent changes to DTC made by RPC security require MSDTC to run as "Ntauthority\networkservice". This is. The default option for NetServer. In WindowsNT and Windows2000, the MSDTC service is run under the "Local System" account by default.

Warning: Improper use of Registry Editor can cause serious problems that may require you to reinstall your operating system. Microsoft is not guaranteed to resolve problems caused by improper use of the Registry Editor. Using Registry Editor requires your own risk.

To change the MSDTC service account from "Local System" to "Ntauthority\networkservice", follow these steps: 1. Run regedt32, browse to Hkey_local_machine\software\ Microsoft\msdtc. Add a DWORD value of TurnOffRpcSecurity, the value data is 1.2. Open a command prompt, run "NETSTOPMSDTC", and then run "NETSTARTMSDTC". 3. Go to the Component Services Administration tool ".

A. Browse to the Start administration tool.

B. Select Component Services.

C. Expand the Component Services tree, and then expand My Computer.

D. Right-click My Computer and choose Properties.

E. In the MSDTC tab, make sure the following options are selected: Network DTC access network management network transaction XA transaction In addition, the DTC login account must be set to "Ntauthority\networkservice".

F. Click OK. This will prompt you to "MSDTC will stop and reboot." All dependent services will be stopped. Please press ' yes ' to continue. Click Yes to continue.

G. Click OK to close the My Computer Properties window.

4. Run Regedt32 again, browse to HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSDTC, and then delete the turnoffrpcsecurity entry. Your MSDTC service should now run under the "Ntauthority\networkservice" account, and this error will not occur again.

More information when network DTC access is disabled under the MSDTC security configuration, if you run a linked server query, the following error is returned:

server:msg 7391, level 16, state 1, line 2 the operation could not be performed because the ole db provider sqloledb was unable to begin a distributed transaction.[ole/db provider returned message:new transaction cannot enlist in the specified transaction coordinator.]

跟踪标记 7300 打开时:
server:msg 7391, level 16, state 1, line 2 the operation could not be performed because the ole db provider sqloledb was unable to begin a distributed transaction.[ole/db provider returned message:new transaction cannot enlist in the specified transaction coordinator.] ole db error trace [ole/db provider sqloledb itransactionjoin::jointransaction returned 0x8004d00a].

The MSDTC service must be set according to the instructions in the "Resolution" section above to prevent this error.

Steps to reproduce the phenomenon

1. Go to the Start Administration tools service.

2. Right-click the Distributed Transaction Coordinator service, go to the Login tab, and select the Local System account under logon status.

3. Stop and restart the service.

4. From Query Analyzer, run the following script:

exec sp_addlinkedserver remote1
exec sp_setnetname remote1, <remote server name>
go
exec remote1.pubs.dbo.sp_executesql ncreate table t (c1 int)
go
set xact_abort on
go
begin tran
insert into remote1.pubs.dbo.t values (1)
commit tran
go

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.