Msg: 8501 - MSDTC unavailable on server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Msg: 8501 - MSDTC unavailable on server

Author

Topic

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-07-16 : 15:12:01

I'm trying to develop a way to check and see if the SQL agent is running on remote servers on a regular basis. (A heartbeat, of sorts.)

What I have right now, is a procedure where each remote server is responsible for contacting the home server and doing a delete/insert on a table that records it's name and has a timestamp (default to getdate()) in it. The part works fine, except when there are communication problems between the remote servers and the home server.

So, I am working on a script that runs on the home server tand attempts to verify if there was just a communications problem, or if the SQL Agent service on the remote server is really down. Here's what I have so far:



alter procedure proc_Verify_Dead_Agent
@srvname varchar(15)
as

exec sp_addlinkedserver @srvname, 'SQL Server'
exec sp_addlinkedsrvlogin @srvname, 'false', null, 'sa', 'bccs93i'

truncate table remote_services
declare @agt_test varchar(4000)
set @agt_test = 'insert into remote_services exec [' + @srvname + '].master.dbo.xp_cmdshell ''net start'''
exec(@agt_test)

declare @agt_ok bit
set @agt_ok = (select count(*) from remote_services where running = '   SQLSERVERAGENT')
 
if @agt_ok = 0 -- Agent is definitely down.
   begin
   insert into notifications
   select @srvname, 'SQL Agent not responding.', getdate()
   end
else
   insert into notifications
   select @srvname, 'Communication problem exists.', getdate()

exec sp_dropserver @srvname
go

The error I'm running into in QA is:
Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'HOME_SERVER' is unavailable.

The SQL Server log has:
Resource Manager Creation Failed: XACT_E_CONNECTION_DOWN

The home server is SQL 2000, sp4 on Win2003, sp1. The remote server is SQL 2000 sp4 on Windows XP, sp2. I can run the procedure itself from query analyzer with no issues, but when I try to run it as a stored proc, I receive the error. DTC is running on both machines and neither of the machines are clustered.

I've seen conflicting reports as to what account the DTC service should be running as. Currently it's a domain account on the home server and LocalSystem on the remote server. Any input on this is appreciated.

Trace flag 7300 did not give me any additional information.

Any further help or nudges in the right direction would be appreciated.

Thanks.

-D.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-16 : 22:41:04

Did you enable dtc network access?

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-07-17 : 16:01:06

It was already enabled. I tried all three logins for the DTC service (Domain login, NetworkService, and LocalService.) Network Service worked, so it's up and running now.

Thanks for your help.

-David.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources