Question :
This might be a simple one, but i searched a lot and unable to find one.
Is there a sql query or a way i can run on registered server to find the list of all sql related services running like, ssis , ssas, ssrs, sql, agent, broswer, etc…
Tried googling out and can find sys.dm_server_services dmv but it does not show all the services running
Answer :
SQL–> Powershell script i got from this article. Maybe something like this?
http://hubpages.com/technology/Using-xp_cmdshell-to-Query-the-Windows-File-System.
I ran this on my desktop’s SSMS using a Registered Server connection to a server in my network.
**Originally made for IPCONFIG testing, but you can just adapt it for net start.
--Enable SQL->PowerShell
EXEC sp_configure 'show advanced options', 1;
GO
Reconfigure;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
Reconfigure
GO
--Run 'net start' command from SQL Server
--1) Create the table
CREATE TABLE #cmdNetStartRun
(outputvalue VARCHAR(3000)
,outputDate DATETIME2 DEFAULT GETDATE());
--2) Run the cmd and insert the output into the table
INSERT INTO #cmdNetStartRun(outputvalue)
exec xp_cmdshell 'net start';
--3) Select from the table
SELECT outputvalue, outputDate
FROM #cmdNetStartRun
WHERE outputvalue LIKE '%SQL%';
--4) Drop the Table
DROP TABLE #cmdNetStartRun;
Powershell would be simplest.
Get-Service "sql*"