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.