This topic describes how to use stored procedures to create a Microsoft SQL Server Agent job.
To create a SQL Server Agent job Execute sp_add_job to create a job.
- Execute sp_add_jobstep to create one or more job steps.
- Execute sp_add_schedule to create a schedule.
- Execute sp_attach_schedule to attach a schedule to the job.
- Execute sp_add_jobserver to set the server for the job.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.
A job in SQL Server Agent consists of Job steps, an owner to provide the security context for the job, and one or more schedules for executing the job.
Steps for creating a new job is as follows:
1. Create a new job, give it a name, database context and add owner
2. Add one or more job steps to job
3. Specify a schedule to run the job (Optionally)
To create a new job, you need to expand SQL Server Agent node within the Object Explorere in SSMS
Right Click on Jobs node and choose new job. The New Job window opens. In this you can define various properties like Name, Category, Owner, Description.
The Job name can be upto 64 characters long. The Job name should clearly define purpose of the Job.
You can assign Job Category to group jobs together based on the types of action they perform. You can use the built-in type job categories that ship with SQL Server or you can create your own job categories.
A descriptive text enables to enter additional details about the job. If certain business rules govern a job, you should specify them in job description to facilitate job-maintenance actions.
On the New job window, in the general tab you specify the job owner, which defines user or group who manage the jobs. The job owner must be member of the sysadmin role to modify the job. If the user is not member of sysadmin role, you need to ensure the job owner should have acces to proxy accounts necessary to execute the step within job.
Create new Job Steps
The high level steps for defining a job are:
1. Create a new job step, specifying name and type.
2. Define the command you want to execute.
3. Define logging and notification Services.
After clicking new, you need to define the job step's properties. For each job step, you nedd to define a name which can be 64 characters. You need to specify the type for each job step. the most common type of job step is Transact-SQl. With this type, you define database context in which to run the T-SQl batch you specify. You can define a simple T-SQl statement to execute a job step but mostly we specify a call to Stored Procedure.Depending on the job step you select, you get configuration options. You can get these options by clicking on Advanced Page in New Job Step window. You can specify actions to be taken when the step completes successfully or when job step fails.
