Dieses Script listet alle MS-SQL Services auf und zeigt deren Status an:
Show/Hidden actionscript3 code
---Listet alle Services und deren Status auf der aktuellen Instanz
---Janke 2018 - FITS
SET nocount ON
IF ( Object_id ('tempdb..#RegResult') ) IS NOT NULL
DROP TABLE #regresult
CREATE TABLE #regresult
(
resultvalue NVARCHAR(4)
)
IF ( Object_id ('tempdb..#ServicesServiceStatus') ) IS NOT NULL
DROP TABLE #servicesservicestatus
CREATE TABLE #servicesservicestatus
(
rowid INT IDENTITY(1, 1),
servername NVARCHAR(128),
servicename NVARCHAR(128),
servicestatus VARCHAR(128),
statusdatetime DATETIME DEFAULT (Getdate()),
physicalsrvername NVARCHAR(128)
)
IF ( Object_id ('tempdb..#Services') ) IS NOT NULL
DROP TABLE #services
CREATE TABLE #services
(
rowid INT IDENTITY(1, 1),
servicename NVARCHAR(128),
defaultinstance NVARCHAR(128),
namedinstance NVARCHAR(128)
)
INSERT INTO #services
VALUES ('MS SQL Server Service',
'MSSQLSERVER',
'MSSQL'),
('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',
'MsDtsServer130',
'MsDtsServer110') /* Ändern 'MsDtsServer110' zu 'MsDtsServer100' for SQL 2008 and früher*/
DECLARE @ChkInstanceName NVARCHAR(128)
DECLARE @ChkSrvName NVARCHAR(128)
DECLARE @REGKEY NVARCHAR(128)
DECLARE @i INT=1
DECLARE @Service NVARCHAR(128)
SET @ChkSrvName = Cast(Serverproperty('INSTANCENAME') AS NVARCHAR(128))
/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/
WHILE ( @i <= (SELECT Max(rowid)
FROM #services) )
BEGIN
IF ( @ChkSrvName IS NULL
OR (SELECT Count(*)
FROM #services
WHERE servicename IN (
'SQL Browser Service - Instance Independent',
'SSIS' )
AND rowid = @i) > 0 )
SELECT @Service = defaultinstance
FROM #services
WHERE rowid = @i
ELSE
SELECT @Service = namedinstance + '$'
+ Cast(Serverproperty('INSTANCENAME') AS VARCHAR(128))
FROM #services
WHERE rowid = @i
SET @REGKEY = 'System\CurrentControlSet\Services\'
+ @Service
INSERT #regresult
(resultvalue)
EXEC master.sys.Xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key= @REGKEY
--PRINT @REGKEY
IF (SELECT resultvalue
FROM #regresult) = 1
BEGIN
INSERT INTO #servicesservicestatus
(servicestatus)
EXEC Xp_servicecontrol
N'QUERYSTATE',
@Service
END
ELSE
BEGIN
INSERT INTO #servicesservicestatus
(servicestatus)
VALUES ('NOT INSTALLED')
END
UPDATE #servicesservicestatus
SET servicename = (SELECT servicename
FROM #services
WHERE rowid = @i),
servername = @@SERVERNAME,
physicalsrvername = (SELECT Cast(Serverproperty(
'ComputerNamePhysicalNetBIOS')AS
VARCHAR(128)))
WHERE rowid = @@identity
TRUNCATE TABLE #regresult
SET @i=@i + 1;
END
/* -------------------------------------------------------------------------------------------------------------*/
SELECT
servername
,servicename
,servicestatus
FROM #servicesservicestatus
Download: