Detecting if DTC is running

I've recently worked on a fix for one of our jobs that starts when the SQL agent is started and proceeds to contact a central server to report the new node the instance is running on. The problem is that sometimes the MSDTC isn't running when SQL agent starts and the job reports a failure because it can't write data over a linked server when the MSDTC isn't running. The following code detects if DTC is running, I use it in a while loop to wait for the DTC. The code is cluster compaitible.

SET NOCOUNT ON

DECLARE @DTC_Found INT

CREATE TABLE #output (line NVARCHAR(4000))

INSERT INTO #output
EXEC master..xp_cmdshell N'net start'

SELECT @DTC_Found = COUNT(1) FROM #output WHERE [line] LIKE '%Distributed Transaction Coordinator%'

IF @DTC_Found < 1
BEGIN
&nbsp;&nbsp;&nbsp; TRUNCATE TABLE #output
&nbsp;&nbsp;&nbsp; INSERT INTO #output
&nbsp;&nbsp;&nbsp; EXEC master..xp_cmdshell N&#39;cluster res&#39;
&nbsp;&nbsp;&nbsp; SELECT @DTC_Found = COUNT(1) FROM #output WHERE [line] LIKE &#39;%MSDTC%Online%&#39;
END

DROP TABLE #output

SELECT @DTC_Found

Share this