MSDTC on server is unavailable

I have a server called SCSS01 with SQL Server 2000 installed
I have another server called NT02 with a named instance of SQL Server 2000 installed - the instance name is NT02_2000
I have set up SCSS01 as a linked server on NT02\NT02_2000
When I attempt to execute a transaction on NT02\NT02_2000 that updates a table on my linked server (SCSS01) I receive the following error
MSDTC on server 'NT02\NT02_2000' is unavailable
I think this is because the MSDTC service is called NT02 and not NT02\NT02_2000
Is this a bug or am I doing something wrong
Any help would be appreciatedIf these are Windows 2003 servers have you installed Network MSDTC?
Check the following:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817064
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Rand
The server named NT02 is w2003 and I have already enables network DTC access - it made no difference
The server named SCSS01 is windows NT4 server
It is the names that look wrong to me - my NT02 server has MSDTC server running on it but when I run the transaction the error message indicates it is looking for an MSDTC server on 'NT02\NT02_2000'
Using SQL Server Service Manager also seems to give strange results when looking at the 'Ditributed Transaction Coordinator' service - no matter which server I look at the status bar shows 'Running \\NT02 - MSDTC
Can you give me some more help - I can provide more details if necessary.|||Hi John,
From your descriptions, I understood that you would like to use Linked
Server with an Instance in SQL Server on Windows 2003. Have I understood
you? If there is anything I misunderstood, please feel free to let me know:)
First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
MSDTC' as it's an machine-level service instead of instance-level service.
Secondly, Randy's suggestion is very helpful. Have you restart the machine
after make that change? Do you use cluster server?
Thirdly, when you are attempting executing an distributed query, what's the
error message? What's your query? Could you run the query standalone in
SCSS01 and get the right result?
At last, the following KB may hit your scenrio, would you please have a
check on them according to your error message? (I keep in mind that you are
using Windows 2003 and I just want you have a look at them if the error msg
meets)
You may receive error message 8525 when you try to run a distributed
transaction on an instance of SQL Server 2000 SP3
http://support.microsoft.com/?id=834849
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2 (839279)
http://support.microsoft.com/?id=839279
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||You have understood my problem correctly.
I undertand that MSDTC is at machine level and my machine name in NT02 so the service status bar showing 'Running \\NT02 - MSDTC' is ok.
I have restarted my machine after enabling network for msdtc - it did not make any difference.
I am not using cluster server.
I can reproduce my problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error message I get when running my transaction is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on NT02\NT02_2000?
Shouldn't it be looking for MSDTC on NT02?
The same query works perfectly if I run it on SCSS01.
I have looked at the kb articles you mentioned but they do not seem to be of any help to my situation.
Do you have any more ideas?
If you need any more details then please let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||Hi John,
Thanks for your prompt updates!
First of all, as we are using distributed query, we should no longer using
BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
to see whether it will resolve your issue?
Secondly, if above could not resolved you issue, would you please show me
your SQL Log files? I have to use them finding the root cause of this
issue. You could only post relative log in the newsgroup :)
Thank you for your patience and cooperation. If you have any questions or
concerns, don?t hesitate to let me know. We are
here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup :)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, donâ't hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup :)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, donâ't hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi John,
To solve this issue, would you please try the following steps?
1. If you are using SQL cluster, make sure MSDTC has been installed on the
cluster
successfully, and make sure you can ping the MSDTC resource's Network name?
The following documents may be helpful to rebuild MSDTC on your cluster
server
HOWTO: Rebuild or Move MSDTC Used with a SQL Failover Cluster
http://support.microsoft.com/?id=294209
2. Please make sure you have configured the MSDTC on the Windows NT/2003 by
trying the steps mentioned below successfully, then try to reboot the
machine.
After you reboot the machine, start MSDTC and then start MSSQLServer.
--Confiuration for MSDTC on Windows 2003
a. Click "Start", point to "All Programs", point to "Administrative Tools",
and then click "Component Services".
b. In the Component Services Wizard, expand "Component Services", and then
double-click "Computers".
c. Right-click "My Computer", and then click "Properties".
d. Click the "MSDTC" tab, and then click "Security Configuration".
e. In the "Security Configuration" dialog box, click to select the "Network
DTC Access" check box.
f. Under "Network DTC Access", click "Network Transactions".
g. Make sure that "DTC Logon Account" is set to "NT
Authority\NetworkService".
--End of Configuration
3. If it still does not work, try to restart the MSDTC on the Windows NT
machine, then restart MSSQLServer on that machine.
4. If it still does not, on both machines, open regedt32.exe, go to
HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC, add a DWORD named
TurnOffRpcSecurity
with the value 1 under this key, then restart MSDTC to see if it works.
If all above still could not resolved issue, what's the detailed SQL Server
and Windows Error message for the failure of running distributed
transaction error?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Changing the DTC Logon Account has solved my problem.
The log on account for my DTC service was set to 'Local System Account'
I assume the problem is that the local system account does not have network access rights.
It seems strange that the default log on account for DTC is the local system account because surely the whole purpose of the DTC service means that it will always need network access rights.
I am very grateful for your speedy response to this problem.
Thank you
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> To solve this issue, would you please try the following steps?
> 1. If you are using SQL cluster, make sure MSDTC has been installed on the
> cluster
> successfully, and make sure you can ping the MSDTC resource's Network name?
> The following documents may be helpful to rebuild MSDTC on your cluster
> server
> HOWTO: Rebuild or Move MSDTC Used with a SQL Failover Cluster
> http://support.microsoft.com/?id=294209
> 2. Please make sure you have configured the MSDTC on the Windows NT/2003 by
> trying the steps mentioned below successfully, then try to reboot the
> machine.
> After you reboot the machine, start MSDTC and then start MSSQLServer.
> --Confiuration for MSDTC on Windows 2003
> a. Click "Start", point to "All Programs", point to "Administrative Tools",
> and then click "Component Services".
> b. In the Component Services Wizard, expand "Component Services", and then
> double-click "Computers".
> c. Right-click "My Computer", and then click "Properties".
> d. Click the "MSDTC" tab, and then click "Security Configuration".
> e. In the "Security Configuration" dialog box, click to select the "Network
> DTC Access" check box.
> f. Under "Network DTC Access", click "Network Transactions".
> g. Make sure that "DTC Logon Account" is set to "NT
> Authority\NetworkService".
> --End of Configuration
>
> 3. If it still does not work, try to restart the MSDTC on the Windows NT
> machine, then restart MSSQLServer on that machine.
> 4. If it still does not, on both machines, open regedt32.exe, go to
> HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC, add a DWORD named
> TurnOffRpcSecurity
> with the value 1 under this key, then restart MSDTC to see if it works.
> If all above still could not resolved issue, what's the detailed SQL Server
> and Windows Error message for the failure of running distributed
> transaction error?
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>