SET NOCOUNT ON;
IF (OBJECT_ID ('tempdb..#RegResult')) IS NOT NULL
);
SQLServerName NVARCHAR(128),
ServiceName NVARCHAR(128),
ServiceStatus VARCHAR(128),
CheckDateTime DATETIME DEFAULT (GETDATE()),
PhysicalSrvName NVARCHAR(128)
);
ID INT IDENTITY(1,1),
ServiceName NVARCHAR(128),
DefaultInstance NVARCHAR(128),
NamedInstance NVARCHAR(128)
);
INSERT #Services
('SQL Server Agent Service','SQLSERVERAGENT','SQLAgent'),
('Analysis Services','MSSQLServerOLAPService','MSOLAP'),
('Full Text Search Service','MSFTESQL','MSSQLFDLauncher'),
('Reporting Service','ReportServer','ReportServer'),
('SQL Browser Service - Instance Independent','SQLBrowser','SQLBrowser'),
('SSIS','MsDtsServer110','MsDtsServer110');
DECLARE
@ChkInstanceName NVARCHAR(128),
@ChkSrvName NVARCHAR(128),
@REGKEY NVARCHAR(128),
@i INT=1,
@Service NVARCHAR(128);
WHILE (@i<=(SELECT MAX(ID) FROM #Services))
IF (@ChkSrvName IS NULL OR (
AND ID = @i) > 0
SELECT @Service= DefaultInstance FROM #Services WHERE ID = @i
ELSE
SELECT @Service= NamedInstance+'$'+CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
FROM #Services WHERE ID = @i
IF (SELECT ResultValue FROM #RegResult) = 1
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC xp_servicecontrol N'QUERYSTATE',@Service
END
ELSE
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
ServiceName = (SELECT ServiceName FROM #Services WHERE ID = @i),
SQLServerName = @@SERVERNAME,
PhysicalSrvName = (
WHERE
ID = @@IDENTITY
SET @i = @i + 1;
SELECT * FROM #ServicesServiceStatus