Now that you’re familiar with stored procedures, it’s time to introduce you to jobs. Jobs are like stored procedures, in that they are a set of steps performed sequentially. Jobs can run T-SQL commands, Integration Services Packages, Analysis Services commands, replication tasks, and even command prompt applications.

And what’s the best part?

These steps are run by the SQL Server Agent and they can be scheduled. That means no more waking up in the middle of the night to run a procedure to index a table, or check for some condition.

You can now make the server work a little harder for you.

To create or manage jobs, you’ll need to have permission, you’ll either need access to the Agent role, or administrator role. You could create your job via SQL Server Management Studio, but you’d then have to script out that change to T-SQL, so you can follow rule number 4. So I’ll skip the graphical method, and skip straight to creating the T-SQL code.

First you have to Create the job.

DECLARE @jobID BINARY(16)

EXEC msdb.dbo.sp_add_job
    @job_name=N'Job Name'
  , @job_id = @jobID OUTPUT
PRINT @jobID

There are other parameters that sp_add_job will take, if you want a full list of that, check out Microsoft’s Books online.

Once you have created the job we’ll move on to adding steps, but I would like to note here you can modify the job options at any time by using sp_update_job. Just keep in mind you’ll need the job_id of the job you wish to change.

Now, let’s add a step to the job we’ve created.

DECLARE @jobID BINARY(16)
SELECT
  @jobID = job_id
FROM msdb..sysjobs
WHERE
  name = 'Job Name'

EXEC msdb.dbo.sp_add_jobstep
    @job_id=@jobId
  , @step_name=N'Do SQL Stuff'
  , @step_id=1
  , @subsystem=N'TSQL'
  , @command=N'SELECT ''Hello, my name is Inigo Montoya, you killed my father...Prepare to die!'''
  , @database_name=N'DB_Name'
  , @flags=0

The SELECT query sets your job ID value for you. Then you’ll need to provide the step name, and the T-SQL query as well as the database name. There are more options available to you. If you need to reference those, please see the Microsoft Books Online.

I’d also like to point out the subsystem step… this is currently set to T-SQL, but you can change this to run other things, like SSIS Packages!

Another note: if you need to remove a step, please see the reference for sp_delete_jobstep.

Now, let’s set the schedule.

DECLARE @jobID BINARY(16)
SELECT
  @jobID = job_id
FROM msdb..sysjobs
WHERE
  name = 'Job Name'

EXEC msdb.dbo.sp_add_jobschedule
    @job_id=@jobId
  , @name=N'Mon-Fri 6:00AM to 7:00PM, every hour'
  , @freq_type=8
  , @freq_interval=62
  , @freq_subday_type=8
  , @freq_subday_interval=1
  , @freq_relative_interval=0
  , @freq_recurrence_factor=1
  , @active_start_date=20100302
  , @active_end_date=99991231
  , @active_start_time=60000
  , @active_end_time=190000

Again, you’ll get the job ID from the SELECT statement, and you’ll have to provide a schedule name. That’s easy part, the rest is a little odd at first.
You have to choose a frequency type. Choose one of the following:

Value	Description
1	Once
4	Daily
8	Weekly
16	Monthly
32	Monthly, relative to freq_interval
64	Run when SQLServerAgent service starts
128	Run when the computer is idle

You’ll also have to choose a frequency interval, this is based on your frequency type. Choose one of the following:

Value of freq_type	Effect on freq_interval
1 (once)	freq_interval is unused.
4 (daily)	Every freq_interval days.
8 (weekly)	freq_interval is one or more of the following (combined with an OR logical operator): 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday
16 (monthly)	On the freq_interval day of the month.
32 (monthly relative)	freq_interval is one of the following: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = Weekday 10 = Weekend day
64 (when SQLServerAgent service starts)	freq_interval is unused.
128	freq_interval is unused.

Then you’ll have to enter a frequency subday type. Again, just choose one of the following:

Value	Description (unit)
0x1	At the specified time
0x4	Minutes
0x8	Hours

Next, you’ll have to give a frequency subday interval, 1 means ignore this parameter. You’ll also need a frequency relative interval, this really only comes into play if you set teh freq_interval to 32 (meaning monthly intervals). You can choose to have something run with one of the following options. If you choose a req_type other than 32, this option is ignored.

Value	Description (unit)
1	First
2	Second
4	Third
8	Fourth
16	Last

Next, set the frequency recurrence factor, the number of weeks or months between the scheduled execution of a job.

The last four options are much easier to understand than those in the middle. @active_start_date is the date you want to start this job being active in yyyymmdd format. @active_end_date of course is your end date (99991231 is the same as never). @active_start_time is the start time in hhmmss format, leaving @active_end_time as your end time.

You also have access to sp_update_schedule and sp_delete_schedule in case you need to modify your schedule.

This is a difficult way to build your jobs, until you have to start rolling them out on multiple servers (development, testing, and production). Then you’ll learn to love this method, since you can script your changes, and store them in version control.

If you have any questions on jobs, let me know, I’m here to help!