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
declare @@machine nvarchar(255)
@service=N'cdo01BkgProc', /* service short name */
@control=129, /* service signal number */
select @sservicetype=case @servicetype
when 0x2 then N'SERVICE_FILE_SYSTEM_DRIVER'
when 0x1 then N'SERVICE_KERNEL_DRIVER'
when 0x10 then N'SERVICE_WIN32_OWN_PROCESS'
when 0x20 then N'SERVICE_WIN32_SHARE_PROCESS'
when 0x100 then N'SERVICE_INTERACTIVE_PROCESS'
select @scurstate=case @curstate
when 0x5 then N'SERVICE_CONTINUE_PENDING'
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'
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
@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