I need to check if a custom Windows Service is running at customer sites. Very easy thing to do with xp_serviceControl, and linked servers. Works great, BUT when I try to store the results of this handy little guy in a table, it fails because SQL Server wants to start a DTC transaction. How do I get around this? I just want to get my hands on the return value.
Here's the error:
"The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "AJLB" was unable to begin a distributed transaction."
Here's the TSQL:
DECLARE @SvcState TABLE(
TimeChecked datetime default(getdate())
INSERT INTO @SvcState(SvcStatus)
exec("ajtb.master.dbo.xp_serviceControl 'querystate', 'SqlServerAGENT'")
SELECT * FROM @SvcState