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

Posted on 2008-10-30
Medium Priority
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

578 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