2

Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?

1
  • Yes Thank you , it is also says to enable xp_cmdshell
    – Albert
    Jul 31, 2020 at 17:59

3 Answers 3

1

If xp_cmdshell is enabled you could run this.

CREATE TABLE #WindowsSvc (results VARCHAR(MAX));
INSERT INTO #WindowsSvc
EXEC xp_cmdshell 'net start'
SELECT results FROM #WindowsSvc WHERE results LIKE '%SQL Server%'
3
  • Thanks this works but is there any way with out enabling xp_cmdshell
    – Albert
    Jul 30, 2020 at 17:27
  • I don't believe there is another way to write T-SQL and acquire that information. The information you are looking for is outside of SQL server at the Operating System. xp_cmdshell permits such communication between SQL server and the OS.
    – nkdbajoe
    Jul 30, 2020 at 18:21
  • Ok thanks for your help.
    – Albert
    Jul 30, 2020 at 18:31
2

The DBATools PowerShell module is very useful for such tasks.

You can use Get-DbaService function from DBATools to get list of SQL related services installed on one or more servers.

Get-DbaService -ComputerName YourServer

0

If you are OK to use xp_cmdshell you can call tasklist which shows running processes and their process_ids

exec xp_cmdshell "tasklist /FO TABLE"

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