I recently had a requirement for an end user to start some SQL Agent jobs on demand. For example, the user maintained reference tables in an MS Access database. After they updated the Acces tables, they needed to run the SQL Agent job that updates the matching tables in SQL Server. However, management did not want to give the user access to SQL Server Management Studio and SQL Agent. What to do?
This looks like a job for Consultant Man! :-) The solution is simple yet effective and the approach can be applied to other scenarios.
The steps are as follows:
- Create a table in SQL Server that holds at least two columns: the job name and a bit flag which is True, i.e. 1, if the user wants the job to run immediately and False, i.e. 0, if they do not.
- Insert rows into the above table with the name of the job in column one and 0 in column two.
- Create an Update trigger on the table that, when fired, tests for the run immediate bit flag being 1, and if it is, runs the job using the stored procedure sp_start_job.
- Test the trigger by updating the bit flag in the table with the job name we want executed to 1.
- The job should execute.
The code is where the rubber hits the road so let's try it.
1) Create the table.
USE Development
GO
/****** Object: Table [dbo].[JobTable] Script Date: 4/28/2015 6:53:55 AM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JobTable](
[JobName] [varchar](100) NOT NULL,
[RunNow] [bit] NULL
)
2) Insert at least one row into the table. We'll do two just to show how this can be scaled to support mutiple jobs.
Insert into dbo.JobTable (JobName, RunNow)
Values ('MyJob',0);
go
Insert into dbo.JobTable (JobName, RunNow)
Values ('MyOtherJob',0);
go
3) Create the update that kicks off the job.
CREATE TRIGGER [dbo].[JobTableUpdateTrigger]
ON [dbo].[JobTable]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
If (select RunNow from Inserted where JobName = 'MyJob')= 1
exec msdb.dbo.sp_start_job N'MyJob';
END
Inserted is a table SQL Server creates to hold the new values of the columns. Above, the trigger selects the RunNow column from Inserted where the JobName = the job we want to run and compares it to 1. So above, if the Inserted RunNow = 1, the job will be executed. Note: We test for the Job Name because we have multiple job names in the table and we want to make sure we run the right one. We could expand the trigger to add another If test that runs the other job in the table when RunNow = 1. The stored procedure sp_start_job is a SQL Server supplied stored procedure to run jobs.
4) Update the row's RunNow column for JobName = 'MyJob' to 1.
Update dbo.JobTable
Set RunNow = 1
Where JobName = 'MyJob';
If the stars are aligned and the house of your zodiac sign is in Jupiter, the job should execute. If you wrote the job correctly, it will even succeed.
One other thing, it's a good idea to reset the RunNow bit after the trigger has fired. So a step in the job should set the value back to Zero as shown in the statement below.
Update dbo.JobTable
Set RunNow = 0
Where JobName = 'MyJob';
With triggers, you have to be careful not to create an infinite loop of updates. The update above will cause the trigger to fire again but since the code tests for a RunNow = 1, it will not start the job again. Note: If you try to do the update in the trigger, you would create an infinite loop, i.e. the update fires the trigger and the trigger fires an update.
Whenever the user wants to run the job, they just need to update the RunNow flag in the table. There are a number of ways this can be done. One way, is to create a linked table in an MS Access database table where the user can set the RunNow flag. Another way, is to give the user a PowerShell script that updates the RunNow flag. If you would like to know how specifically to do that, please leave a comment so I know there is interest and I can explain that in another blog entry. For users with access to SQL Server Management Studio, you can just right mouse click on the table, select Edit Top 200 Rows and update the RunNow flag as shown below.
Figure 1 - SQL Server Management Studio - Table Edit
That's all there is to it. This is a very powerful because it can be applied to other situations. For example, you could use this approach to kick off jobs from another job, a stored procedure, PowerShell, or anything that can update a SQL Server table. By simply by updating the RunNow bit column of the row with the job name you want to run, the job will execute. The sp_start_job has parameters to run jobs on other SQL Server instances so you can even use this approach to submit jobs on another server.
