Manual End User SQL Job Triggering

Recently I was tasked to allow a user to manually trigger a SQL job, from a User Interface. Luckily, there’s a stored procedure for that in the msdb database, called “sp_start_job“, that does just that. While this is usually a pretty rare scenario, I opted to design a more dynamic UI to allow for similar situations being added just by configuration.

I started by creating a lookup table to store the list of jobs the end user can execute. I can leverage this table for the issues of what jobs should be displayed to the user and validating on the server side that the job requested to be executed is in fact a job the user can execute.

For the display, I created a simple view that checks sysjobs, sysjobhistory, and sysjobactivity tables to pull back the effective status. On the front end, using AngularJS I was able to make a quick layout to display panels of each job, with visual indicators as to the status.

 

SELECT        
	dt.Id, 
	CASE 
		WHEN h.run_status = 0 THEN 'Failed' 
		WHEN h.run_status = 1 THEN 'Succeeded' 
		WHEN h.run_status = 2 THEN 'Retry' 
		WHEN h.run_status = 3 THEN 'Cancelled' 
		WHEN h.run_status = 4 THEN 'In Progress' 
		ELSE 'Unknown'
    END AS JobStatus, 
	j.name AS [Name], 
	activity.run_requested_date AS LastRun
FROM           
	msdb.dbo.sysjobs AS j 
LEFT JOIN
	(SELECT 
			job_id, 
			MAX(instance_id) AS mostrecent
        FROM msdb.dbo.sysjobhistory AS hist
        GROUP BY job_id) AS hmostrecent 
	ON hmostrecent.job_id = j.job_id 
LEFT JOIN msdb.dbo.sysjobhistory AS h 
	ON h.job_id = hmostrecent.job_id AND h.instance_id = hmostrecent.mostrecent 
LEFT JOIN
	(SELECT        job_id, MAX(run_requested_date) AS mostrecent
		FROM            msdb.dbo.sysjobactivity AS act
		GROUP BY job_id) AS amostrecent 
	ON amostrecent.job_id = j.job_id 
LEFT JOIN msdb.dbo.sysjobactivity AS activity 
	ON j.job_id = activity.job_id AND activity.run_requested_date = amostrecent.mostrecent 
INNER JOIN Utilities.lkDataTasks AS dt 
	ON dt.Name = j.name

 

<div class="detail panel panel-default" style="margin-right: 20px; float:left;" ng-repeat="t in vm.details" ng-style="vm.getColor(t.jobStatus)">
    <div class="form-inline panel-body">
        <div class="locked">
            <div class="overlay">
                <div><h4>{{t.name}}</h4></div>
                <hr />
                <div class="display-row">Status: {{t.jobStatus}}</div>
                <div class="display-row">Last Run: {{t.lastRun | date:'MMM d, y h:mm:ss a'}}</div>
                <br/>
                <div class="display-row" style="text-align:center;">
                    <div><button class="btn btn-primary" ng-click="vm.execute(t.name)" ng-disabled="t.jobStatus == 'In Progress'">{{t.jobStatus != 'In Progress' ? 'Execute Task' : t.jobStatus}}</button></div>
                </div>
            </div>
        </div>
    </div>
</div>

 

vm.getColor = function (condition) {
    if (condition == "Retry") {
        return { "background-color": "orange" };
    }
    if (condition == "Cancelled") {
        return { "background-color": "lightgray" };
    }
    if (condition == "Succeeded") {
        return { "background-color": "#7fb822" };
    }
    if (condition == "In Progress") {
        return { "background-color": "lightblue" };
    }
    if (condition == "Failed") {
        return { "background-color": "#ff9191" };
    }
};

The result looks like this:

As previously mentioned, after the execute task button is clicked, I have the job name validated server side, so the user can’t execute any arbitrary job. Actually executing the job is really easy, as it is just a matter of calling the msdb.dbo.sp_start_job previously mentioned and passing in the respective job name:

DECLARE @ReturnCode tinyint 
EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name = @job_name1; 
SELECT @ReturnCode GO

While this may be overkill for most scenarios, it fit my use case and helped empower the end user to be able to run the SQL job themselves.