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.

Related Articles

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server Version (29 January 2009)

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Alerts for when Login Failures Strike (14 July 2008)

Using xp_ReadErrorLog in SQL Server 2005 (12 May 2008)

Moving the tempdb database (5 November 2007)

Other Recent Forum Posts

Question about having and where error with my code (85m)

Copying/writing data from one table to another (8h)

SQL Server cannot 'see' my dll (10h)

Need to ignore dashes when using the where statement in SQL - example provided (12h)

INSERT INTO query without duplicates (15h)

Import HTML table into SQL (20h)

Sql 2000 (1d)

Re-Arrange Table (summarize only certain rows) (1d)

- Advertisement -