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

Return error of a timeouted server (3 Replies)

HOW TO SELECT LAST ROWS IN GROUP BY CLAUSE (6 Replies)

Count (2 Replies)

Delete and merge duplicate records from joined tab (2 Replies)

select case when help (2 Replies)

Cannot perform an aggregate function on an express (2 Replies)

Randomly Assign to Group (6 Replies)

Conditional Join creating duplicates (6 Replies)

Subscribe to SQLTeam.com

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

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -