2

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

  • 4
    This is probably better suited to PowerShell, unfortunately. – Erik Darling Aug 7 '17 at 16:04
1

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;
0

Powershell would be simplest.

Get-Service "sql*"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.