xp_servicecontrol in SQL Server

Problem Statement:

While I was automating a new server preparation step, one of my requirement is to make sure “MSDTC” service is up and running on the server. To enable this check, we can use an extended stored procedure – xp_servicecontrol. There are two parameters to this procedure as below.

It is important to understand that this extended procedure is an undocumented one which means, it cannot be used for a development purpose, this can be anytime removed from the product by Microsoft. However, this can be used as it warranted.

xp_servicecontrol @Action = N’Action’ @ServiceName = N’Service Name’

Action Description
start To start the service if it is not running. If the service is already in running state, an error is raised.
stop To stop the service if it is running. If the service is not running, an error is raised.
pause To pause a running service. An error is raised if the service is not running. Remember not all services can be paused.
continue To continue running a paused service. An error is raised if the serviced is not in paused state.
querystate To return the current state of the service.

Code:

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

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

One thought on “xp_servicecontrol in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s