SQL Server Job Scheduling

Job Scheduling

A. Creating and viewing the jobs from management studio:

SQL Server Agent should be up and running:

B. Creation of jobs:

C. Executing a job through an SP:

EXEC msdb.dbo.sp_start_job @job_name=N'test'

Some of the SPs that are called internally by SQL Server while creating the jobs are:

msdb.dbo.sp_add_category

msdb.dbo.sp_add_job

msdb.dbo.sp_add_jobstep

msdb.dbo.sp_update_job

msdb.dbo.sp_add_jobschedule

msdb.dbo.sp_add_jobserver

Deleting the job through T-SQL:

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'test1')

EXEC msdb.dbo.sp_delete_job @job_name=N'test1', @delete_unused_schedule=1

All the jobs are stored in msdb database.

D. Executing an SQL server job through SSIS:

The connection should be pointed to the server where the job is placed and the particular job to be executed is selected:

E. History of jobs:

Check the history of jobs to check why the job failed or whether it succeeded:

F. Creation of a job to execute an SSIS package:

Select type as “SQL Server Integration Services Package”

If the package is stored in the file system, then select file system as the package source and browse through to the dtsx package. Else if the package is stored in the SQL server, then select SQL Server as the package source: