Date: 02/11/13 (SQL Server)    Keywords: sql

This is in response to my question on 2/4/2013 for SQL Version 2000 (should work in subsequent versions if you follow my comments)

Design:
User Table Created w/ Trigger
  CREATE TABLE [dbo].[prod_support_job_queue]  (
    [job_name]     sysname NOT NULL,
    [step_id]      int NOT NULL CONSTRAINT [DF__prod_supp__step___4959E263]  DEFAULT (1),
    [action]       nvarchar(6) NOT NULL,  (Must be either START, CANCEL, or STOP)
    [ntlogin]      nvarchar(32) NULL, --used to log who made the request
    [log_date]     datetime NULL,
    [processed]    char(1) NOT NULL CONSTRAINT [DF_prod_support_job_queue_processed]  DEFAULT ('N')
    )
ON [PRIMARY]

CREATE TRIGGER [dbo].[ti_job_queue] on [dbo].[prod_support_job_queue]
for insert
as
   set nocount on

   if (
      update(job_name)
   )
   begin
      declare @username varchar(30)
      declare @log_date datetime
      declare @job_name sysname
      
      -- Get the user's attributes.
      select
        @username = loginame
      from
         master..sysprocesses
     where spid = @@spid

    select @log_date = getdate()
    select @job_name = job_name from inserted
    
    update prod_support_job_queue
    set log_date=@log_date,
         ntlogin=@username
    where
         processed ='N'
    and
         job_name=@job_name
end


Procedures:

• check_job_queue - fires off via scheduled SQL job.  It reads from the prod_support_job_queue table
• make_job_request - procedure exposed to the production support team.  This helps them insert records into the prod_support_job_queue table
• sp_isJobRunning - (Modified this procedure from THIS publicly available code in order for it to run on SQL 2000 )

1. The user makes his request via the make_job_request stored procedure.  He is required to enter a valid job name, action (which is either START, STOP, or CANCEL)  
2. check_job_queue runs every 10 minutes for check for new actions in the prod_support_job_queue table.  It utilizes system stored procedures in msdb to start and stop jobs.  For the CANCEL command, a simple update statement is issued to the processed field to exclude it from further processing checks.
3. sp_IsJobRunning is called by check_job_queue in order to see if the requested job is already running before issuing any commands

Source: http://sqlserver.livejournal.com/77452.html