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
TRUNCATE TABLE #output
INSERT INTO #output
EXEC master..xp_cmdshell N'cluster res'
SELECT @DTC_Found = COUNT(1) FROM #output WHERE [line] LIKE '%MSDTC%Online%'
END
DROP TABLE #output
SELECT @DTC_Found