metroman17 (7/1/2015)

---

DECLARE @ServiceStatus TABLE

(ServerName varchar(128)

,ServiceName varchar(128)

,StatusOfService varchar(128)

,StatusAsOn datetime)

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'msdtc'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='Microsoft Distributed Transaction Coordinator'

,StatusAsOn=GETDATE()

--WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'MSSQLServer'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='MSSQL Server'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'MSSQLServerADHelper'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server Active Directory Helper'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server Agent'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'MSSQLServerOLAPService'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server OLAPService'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'SQLBrowser'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server Browser'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'MSSQLFDLauncher'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server Full Text Search'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

--INSERT INTO @ServiceStatus (StatusOfService)

--EXEC master..xp_servicecontrol 'QueryState', 'MsDtsServer'

--UPDATE @ServiceStatus

-- SET ServerName=cast(@@SERVERNAME as varchar(128))

-- ,ServiceName='SQL Server Integration Services'

-- ,StatusAsOn=GETDATE()

--WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'ReportServer'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server Reporting Services'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)

EXEC master..xp_servicecontrol 'QueryState', 'SQLWriter'

UPDATE @ServiceStatus

SET ServerName=cast(@@SERVERNAME as varchar(128))

,ServiceName='SQL Server VSS Writer'

,StatusAsOn=GETDATE()

WHERE ServerName IS NULL

SELECT * FROM @ServiceStatus