How to send control commands to a remote Win32 services from within a stored procedure

Posted on 2008-10-30
Last Modified: 2013-12-04
Hi experts,

in one of our web-based software products, we launch long-running background processes from within T-SQL code in stored procedures. We do this by calling an Extended Stored Procedure 'master..xp_svcctl' written in C (source partly shown below) which sends a service control command to a Win32 Service written in C# residing on another machine in the same domain. The service then connects to SQL Server, obtains job details from the database and starts to work (e.g. converting documents to PDF, or something like that). So the service control command is needed only to signal that there is a new "job in the queue", we do not need to pass any parameters, only the signal, and it works like a charm since many years in critical production environments.

Now we are about to setup this software on a load-balanced, virtualized cluster environment and the customer does not allow to install any extended stored procedures and we look for a workaround. Also using a managed code assembly is not an option.

Do you have a solution which would allow to keep the Win32 Service as the recipient of the service control command? Is there a best practice to send service control commands other than Start/Stop/Pause from SQL Server to remote machines, maybe via Notification Services?
1. What the code in the extended stored proc does:



ServiceController sc = new ServiceController("cdo01BkgProc");

sc.ExecuteCommand(129); /* >128=range of user-defined service control codes */




2. T-SQL to consume and diagnose the service:

set nocount on



  @servicetype int,

  @curstate int,

  @controlsaccepted int,

  @exitcode int,

  @specificexitcode int,

  @checkpoint int,

  @waithint int,

  @ret int


declare @@machine nvarchar(255)

select @@machine=N'remote_server_name'


exec master..xp_svcctl


  @service=N'cdo01BkgProc', /* service short name */

  @control=129, /* service signal number */

  @servicetype=@servicetype OUTPUT,

  @curstate=@curstate OUTPUT,

  @controlsaccepted=@controlsaccepted OUTPUT,

  @exitcode=@exitcode OUTPUT,

  @specificexitcode=@specificexitcode OUTPUT,

  @checkpoint=@checkpoint OUTPUT,

  @waithint=@waithint OUTPUT,

  @result=@ret OUTPUT



  @sservicetype nvarchar(255),

  @scurstate nvarchar(255),

  @scontrolsaccepted nvarchar(2048)


select @sservicetype=case @servicetype


  when 0x1 then N'SERVICE_KERNEL_DRIVER'

  when 0x10 then N'SERVICE_WIN32_OWN_PROCESS'

  when 0x20 then N'SERVICE_WIN32_SHARE_PROCESS'





select @scurstate=case @curstate


  when 0x6 then N'SERVICE_PAUSE_PENDING'

  when 0x7 then N'SERVICE_PAUSED'

  when 0x4 then N'SERVICE_RUNNING'

  when 0x2 then N'SERVICE_START_PENDING'

  when 0x3 then N'SERVICE_STOP_PENDING'

  when 0x1 then N'SERVICE_STOPPED'




select @scontrolsaccepted=N''

if @controlsaccepted & 0x10=0x10 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_NETBINDCHANGE' end

if @controlsaccepted & 0x8=0x8 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_PARAMCHANGE' end

if @controlsaccepted & 0x2=0x2 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_PAUSE_CONTINUE' end

if @controlsaccepted & 0x100=0x100 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_PRESHUTDOWN' end

if @controlsaccepted & 0x4=0x4 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_SHUTDOWN' end

if @controlsaccepted & 0x1=0x1 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_STOP' end

if @controlsaccepted & 0x20=0x20 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_HARDWAREPROFILECHANGE' end

if @controlsaccepted & 0x40=0x40 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_POWEREVENT' end

if @controlsaccepted & 0x80=0x80 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_SESSIONCHANGE' end

select @scontrolsaccepted=LTrim(@scontrolsaccepted)



  @ret as ret,

  @servicetype as servicetype,

  @sservicetype as sservicetype,

  @curstate as curstate,

  @scurstate as scurstate,

  @controlsaccepted as controlsaccepted,

  @scontrolsaccepted as scontrolsaccepted,

  @exitcode as exitcode,

  @specificexitcode as specificexitcode,

  @checkpoint as [checkpoint],

  @waithint as waithint

Open in new window

Question by:dnebel
  • 2
LVL 86

Expert Comment

ID: 22842771
One thing is that the remote server name in your above example does not follow MS' naming conventions. These are




if you are on a real domain or


if it is a workgroup.

Author Comment

ID: 22843025

thanks for your response. You are right, but this "remote_server_name" I wrote was only a placeholder in my code sample meaning that the actual server name should go there. At runtime, it will be something like "LUNATICUS\SERVER03".

The problem is not that the code would not work as it is - it does;
the problem is that the customer refuses to allow use of extended stored procedures and
we must find a workaround.
SQLServerAgent in connection with xp_cmdshell is also not an option because of security considerations.

Additional Info:

Put more simple, I need a way to signal a remote service process (.NET 1.1 or higher on Windows Server, may run as NETWORK SERVICE or as a local user or as a domain user whatever is required)
and my starting point is a plain T-SQL query, ideally code in a stored procedure.

Accepted Solution

dnebel earned 0 total points
ID: 23013547
We've been able to solve the matter by circumvention. The piece of code was ported to the application's source language and integrated into it. Now the signal is sent from the application server, no longer from the database server. That does not address the original problem albeit it makes sense (this is an application task, not a database task)
Option Explicit


    dwServiceType As Long

    dwCurrentState As Long

    dwControlsAccepted As Long

    dwWin32ExitCode As Long

    dwServiceSpecificExitCode As Long

    dwCheckPoint As Long

    dwWaitHint As Long

End Type

Private Declare Function OpenSCManager Lib "advapi32.dll" Alias "OpenSCManagerA" (ByVal lpMachineName As String, ByVal lpDatabaseName As String, ByVal dwDesiredAccess As Long) As Long

Private Declare Function CloseServiceHandle Lib "advapi32.dll" (ByVal hSCObject As Long) As Long

Private Declare Function OpenService Lib "advapi32.dll" Alias "OpenServiceA" (ByVal hSCManager As Long, ByVal lpServiceName As String, ByVal dwDesiredAccess As Long) As Long

Private Declare Function StartService Lib "advapi32.dll" Alias "StartServiceA" (ByVal hService As Long, ByVal dwNumServiceArgs As Long, ByVal lpServiceArgVectors As Long) As Long

Private Declare Function ControlService Lib "advapi32.dll" (ByVal hService As Long, ByVal dwControl As Long, lpServiceStatus As SERVICE_STATUS) As Long

Private Const GENERIC_EXECUTE As Long = &H20000000

Private Const SERVICE_CONTROL_STOP As Long = 1

Private Const SERVICE_CONTROL_PAUSE As Long = 2


Private Const SERVICE_CONTROL_BKGPROCDEQUE As Long = 129 'ServiceCommands.setsignal_dequepoll = 129

Public Function ServiceCommand(ByVal MachineName As String, ByVal ServiceName As String, ByVal iCommand As Long, ByRef sErr As String) As Boolean

    Dim hSCM As Long

    Dim hService As Long

    Dim Res As Long

    Dim lpServiceStatus As SERVICE_STATUS

    'open a connection to the Service Control Manager, exit if error

    hSCM = OpenSCManager(MachineName, vbNullString, GENERIC_EXECUTE)

    If hSCM = 0 Then

        sErr = "Failed to open the service control manager in an attempt to send control command " & CStr(iCommand) & " to the """ & ServiceName & """ service with code " & CStr(Err.LastDllError)

        Exit Function

    End If

    'open the specified service, exit on error

    hService = OpenService(hSCM, ServiceName, GENERIC_EXECUTE)

    If hService = 0 Then

        sErr = "Failed to open the """ & ServiceName & """ service with code " & CStr(Err.LastDllError)

        GoTo Cleanup

    End If

    'send the service command

    Res = ControlService(hService, iCommand, lpServiceStatus)

    If Res = 0 Then


        sErr = "Failed to send the control command " & CStr(iCommand) & " to the """ & ServiceName & """ service with code " & CStr(Err.LastDllError)

        GoSub Cleanup

        Exit Function

    End If


    GoSub Cleanup

    ServiceCommand = True

    Exit Function


    'free handles

    If hService Then CloseServiceHandle hService

    CloseServiceHandle hSCM


End Function

Open in new window


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
Sending a Secure fax is easy with eFax Corporate ( First, just open a new email message. In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now