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!
SQLZealots 