Controlling Jobs in Transact-SQL

By Bill Graziano on 18 September 2000 | Tags: Administration

We've had a couple of questions about "fire and forget" processing and exporting data to alternative formats. One of the easiest way to accomplish these tasks is using JOBS. They can easily be set up and executed from within Enterprise Manager and just as easily controlled from Transact-SQL.

We'll cover a series of system stored procedures to allow you control SQL Server Agent jobs. The first stored procedure you want is sp_start_job. The syntax goes something like this:

sp_start_job [@job_name = ] 'job_name', [@server_name = ] 'server_name'

This will launch a job and immediately return control back to you. By default anyone in the public role can execute this job. The stored procedure will return a 1 for failure and a 0 for success. You may have noticed the server_name parameter. You can use this to launch jobs on target servers also. You could launch a batch update like this:

sp_start_job @job_name = 'BatchUpdate'

Now that you've started the job you can check on the status by using sp_help_job. The key syntax points are:

sp_help_job [@job_name = ] 'job_name'

This will return a whole bunch of columns. The main one you need to be concerned with is current_execution_status. It can have the following values:

0 - Returns only those jobs that are not idle or suspended.
1 - Executing.
2 - Waiting for thread.
3 - Between retries.
4 - Idle.
5 - Suspended.
7 - Performing completion actions.

sp_help_job has many more options and I'd encourage you to look in Books Online for more details. It can also give you detailed information about job schedules.

If you wish to cancel a running job you can use sp_stop_job. The basics of it's syntax is:

sp_stop_job [@job_name = ] 'job_name'

It will also take parameters for server_name like the other stored procedures.

That's really the basics of controlling jobs from within Transact-SQL.