xp_servicecontrol Return value

Question

I'd like to use something similar to the following:

EXEC xp_servicecontrol N'querystate', N'WinService XYZ'

which returns a "Current Service State" of "Running" if that Service, "WinService XYZ" is running. If it is not running, I want it to return "Offline".

I'd like to wrap that command into a stored procedure so the return value is either "Running" or "Offline"

How do I get the value "Running" returned ?

If I do this:

Declare @v varchar(max)
EXEC @v=xp_servicecontrol N'querystate', N'WinService XYZ'

select @v

the value returned is: 0 which just shows that the command executed successfully.
I'd like Select @v to return: "Running" or "Offline"

Answer 1

Hi @vsslasd ，

Please refer below:

CREATE TABLE #ServicesStatus  
(   
Status varchar(50)  
)  
  
INSERT #ServicesStatus   
EXEC xp_servicecontrol N'QUERYSTATE',N'WinService XYZ'  
  
Declare @v varchar(max)  
select @v=status from #ServicesStatus  
select @v  

OR:

declare @ServicesStatus table  
(   
Status varchar(50)  
)  
  
INSERT @ServicesStatus   
EXEC xp_servicecontrol N'QUERYSTATE',N'WinService XYZ'  
  
Declare @v varchar(max)  
select @v=status from @ServicesStatus  
select @v  

Output:
Running.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table