August 19, 2013 at 4:40 pm
Hi Fnds,
I am looking to find out ssis, ssrs, ssas installed on which server, i have several servers and i want find out those services on all servers. can any one help me?
Thanks
Really it'll be great help, if anyone can help?????
Cheers
mac
August 20, 2013 at 12:04 am
You can connect to the server(s) and run services.msc to see which services are installed. When SSAS is installed, service "MSSQLServerOLAPService" will be available. When SSRS is installed, service "ReportServer" will be available. For SSIS the service "MsDtaServer100" will be available, but the name of the service depends on the version installed.
You could also write a powershell script using the Get-Service cmdlet to do the above (http://technet.microsoft.com/en-us/library/ee176858.aspx).
August 20, 2013 at 2:40 am
Hi Friend,
you right, but i am looking to script out to find out remotely on number of servers. also i want one script that can bring ssis, ssrs, ssas services installed or not. anyway thanks for your help mate.
if you know anything about my requirement please let me know.
cheers
August 20, 2013 at 2:58 am
Have you looked at the Microsoft Assessment and Planning Toolkit - it scans your network and produces a load of reports about what SQL instances etc are running.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 20, 2013 at 2:58 am
I've used WMIC to read the status of services on remote servers. But that was written a while ago. If I had to do it all over again, I would try to accomplish it with powershell.
Below is the main part of the T-SQL code I used to execute WMIC through XP_CMDSHELL. I executed this from a central management server. You can put this code fragment inside a loop of services and/or a loop of servers.
if object_id('tempdb..#result') IS NOT NULL
drop table #result
create table #result
(
servername nvarchar(125)
, [service] varchar(50)
, [state] varchar(150)
, measure_date datetime
)
-- put code below inside one or more loops to query multiple servers and/or services
set @servername = '{servername}'
set @credentials = '/user:"{domain\account}" /password:"{password}"')
set @service = 'ReportServer'-- or 'MSSQLServerOLAPService' or 'MsDtsService100'
-- build WMIC command line
set @command = 'wmic' + @credentials + '/NODE:"' + @servername + '" SERVICE where name="' + @service + '" GET state'
-- execute command line and capture output
insert into #result (state)
exec xp_cmdshell @command
-- update output with additional information
update #result
set servername = @servername
, [service] = @service
, [state] = replace([state], char(13), '')-- remove 'carriage return' character
, measure_date = getdate()
where servername IS NULL
and [service] IS NULL
August 20, 2013 at 4:42 am
Hi fnd,
Thanks and i have not tried so far, but i'll check now.
cheers
August 20, 2013 at 4:44 am
Hi Mate,
Thanks very much and i'll try now, get back to you very soon.
cheers
August 21, 2013 at 8:51 am
Hi Mate,
I have another issue on my question, please find code below
and script is returning all services on 2005 and above, but the problem is we have 2000 server as well.
do you have any idea and i can see what the problem is (in 2000 registry what they call for services like ssis, ssrs ).
SET NOCOUNT ON
CREATE TABLE #RegResult
(
ResultValue NVARCHAR(4)
)
CREATE TABLE #ServicesServiceStatus
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus VARCHAR(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)
DECLARE
@ChkInstanceName NVARCHAR(128) /*Stores SQL Instance Name*/
,@ChkSrvName NVARCHAR(128) /*Stores Server Name*/
,@TrueSrvName NVARCHAR(128) /*Stores where code name needed */
,@SQLSrv NVARCHAR(128) /*Stores server name*/
,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/
,@DTS NVARCHAR(128) /*Store SSIS Service Name */
,@FTS NVARCHAR(128) /*Stores Full Text Search Service name*/
,@RS NVARCHAR(128) /*Stores Reporting Service name*/
,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/
,@OLAP NVARCHAR(128) /*Stores Analysis Service name*/
,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = 'MSSQLSERVER'
SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL'
SELECT @rs = 'ReportServer'
SELECT @SQLAgent = 'SQLSERVERAGENT'
SELECT @SQLSrv = 'MSSQLSERVER'
END
ELSE
BEGIN
SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @SQLSrv = '$'+@ChkSrvName
SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL' + @SQLSrv
SELECT @rs = 'ReportServer' + @SQLSrv
SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
SELECT @SQLSrv = 'MSSQL' + @SQLSrv
END
/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/
EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
UPDATE #ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/
EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
UPDATE #ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
UPDATE #ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Integration Service Section ----------------------------------------------*/
IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'
IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@DTS
UPDATE #ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Reporting Service Section ------------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@RS
UPDATE #ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Analysis Service Section -------------------------------------------------*/
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @OLAP = 'MSSQLServerOLAPService'
END
ELSE
BEGIN
SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
END
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
UPDATE #ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
UPDATE #ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* -------------------------------------------------------------------------------------------------------------*/
SELECT ServiceName AS 'SQL Server Service'
,ServiceStatus AS 'Current Service Status'
,StatusDateTime AS 'Date/Time Service Status Checked'
FROM #ServicesServiceStatus
if u get any idea please let me know
cheers
April 28, 2016 at 9:58 am
modified above code
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','MsDtsServer110','MsDtsServer110') /* change 'MsDtsServer110' to 'MsDtsServer100' for SQL 2008 and accordingly*/
DECLARE @ChkInstanceName NVARCHAR(128) /*Stores SQL Instance Name*/,@ChkSrvName NVARCHAR(128) /*Stores Server Name*/
,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
,@i INT=1 ,@Service NVARCHAR(128)
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(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) /*Detecting staus of SQL Sever service*/
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 *FROM #ServicesServiceStatus
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply