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’
||To start the service if it is not running. If the service is already in running state, an error is raised.
||To stop the service if it is running. If the service is not running, an error is raised.
||To pause a running service. An error is raised if the service is not running. Remember not all services can be paused.
||To continue running a paused service. An error is raised if the serviced is not in paused state.
||To return the current state of the service.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
declare @ServicesStatus table
EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc'
Declare @v varchar(max)
select @v=status from @ServicesStatus
One thought on “xp_servicecontrol in SQL Server”