Controlling Jobs in Transact-SQL

By Bill Graziano on 18 September 2000 | 2 Comments | 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.

Discuss this article: 2 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

eat scrumptious caffeine using these guidelines (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

eat scrumptious caffeine using these guidelines (0 Replies)

?????? ??????????? ?????? 2017 (0 Replies)

WITH EXECUTE_AS not working for sproc. (9 Replies)

Data loss during sql cluster failover (3 Replies)

How to make Ajax Accordian Visible based on dropdo (2 Replies)

Corrupt SMALL transaction log backups in Log Shipp (3 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -