In this post you will see two major sections:
I. Detailed description for every needed field in system stored procedures.
II. Full working example related to above description. Here you need only to replace the names if the default configuration is suitable.
I. To create a SQL Server Agent job you need the folowing system stored procedures to be executed. Please note that the order of execution is important.
- Declarations and initialization
- 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
- Optional you can delete the job
USE msdb DECLARE @JobId uniqueidentifier DECLARE @ServerName VARCHAR(200) DECLARE @DBName VARCHAR(200) DECLARE @ScheduleUID uniqueidentifier DECLARE @ScheduleId INT SELECT @ServerName = 'ServerName' SELECT @DBName = N'DatabaseName' |
EXEC sp_add_job @job_name = N'My Sql Job', |
The name of the job. The name must be unique and cannot contain the percent (%) character. job_name is nvarchar(128), with no default
@enabled = 1, |
Indicates the status of the added job. enabled is tinyint, with a default of 1 (enabled). If 0, the job is not enabled and does not run according to its schedule; however, it can be run manually.
@description = N'My Job Description', |
The description of the job. description is nvarchar(512), with a default of NULL. If description is omitted, “No description available” is used.
@owner_login_name = 'sa', --should be left like this to not have problems with user rights @job_id = @jobid OUTPUT |
EXEC sp_add_jobstep @job_id = @JobId, |
The identification number of the job to which to add the step. job_id is uniqueidentifier, with a default of NULL.
@step_id = 1, |
The sequence identification number for the job step. Step identification numbers start at 1 and increment without gaps. If a step is inserted in the existing sequence, the sequence numbers are adjusted automatically. A value is provided if step_id is not specified. step_id is int, with a default of NULL.
@step_name = N'SQL Job Step 1', |
The name of the step. step_name is sysname, with no default.
@subsystem = N'TSQL', |
The subsystem used by the SQL Server Agent service to execute command. subsystem is nvarchar(40), and can be one of these values.
| Value | Description |
|---|---|
| ‘ACTIVESCRIPTING‘ | Active Script |
| ‘CMDEXEC‘ | Operating-system command or executable program |
| ‘DISTRIBUTION‘ | Replication Distribution Agent job |
| ‘SNAPSHOT‘ | Replication Snapshot Agent job |
| ‘LOGREADER‘ | Replication Log Reader Agent job |
| ‘MERGE‘ | Replication Merge Agent job |
| ‘QueueReader‘ | Replication Queue Reader Agent job |
| ‘ANALYSISQUERY‘ | Analysis Services query (MDX, DMX). |
| ‘ANALYSISCOMMAND‘ | Analysis Services command (XMLA). |
| ‘Dts‘ | Integration Services package execution |
| ‘PowerShell‘ | PowerShell Script |
| ‘TSQL‘ (default) | Transact-SQL statement |
@command = N'EXEC StoredProc', |
The commands to be executed by SQLServerAgent service through subsystem. command is nvarchar(max), with a default of NULL. SQL Server Agent provides token substitution that gives you the same flexibility that variables provide when you write software programs.
@on_success_action = 1, --Quit with success(default) |
The action to perform if the step succeeds. success_actionis tinyint, and can be one of these values.
| Value | Description (action) |
|---|---|
| 1 (default) | Quit with success |
| 2 | Quit with failure |
| 3 | Go to next step |
| 4 | Go to step on_success_step_id |
@on_fail_action = 2, --Quit with failure(default) |
@database_name = @DBName, |
The name of the database in which to execute a Transact-SQL step. database is sysname, with a default of NULL, in which case the master database is used. Names that are enclosed in brackets ([ ]) are not allowed. For an ActiveX job step, the database is the name of the scripting language that the step uses.
@database_user_name = NULL -- job owner's user context |
EXEC sp_add_schedule @schedule_name = N'Sth schedule', |
The name of the schedule. schedule_nameis sysname, with no default.
@enabled = 1, |
Indicates the current status of the schedule. enabledis tinyint, with a default of 1 (enabled). If 0, the schedule is not enabled. When the schedule is not enabled, no jobs will run on this schedule.
@freq_type = 4, --daily |
A value indicating when a job is to be executed. freq_typeis int, with a default of 0, and can be one of these values.
| 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 |
@freq_interval = 1, --every day |
The days that a job is executed. freq_interval is int, with a default of 1, and depends on the value of freq_type.
| 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. |
@freq_subday_type = 0x8, --hours |
Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.
| Value | Description (unit) |
|---|---|
| 0x1 | At the specified time |
| 0x2 | Seconds |
| 0x4 | Minutes |
| 0x8 | Hours |
@freq_subday_interval = 1, --each hour |
The number of freq_subday_type periods to occur between each execution of a job. freq_subday_intervalis int, with a default of 0. freq_subday_interval is ignored in those cases where freq_subday_type is equal to 1. Note: Interval should be longer than 10 seconds.
@active_start_date = '20110610', --YYYYMMDD |
The date on which execution of a job can stop. active_end_dateis int, with a default of 99991231, which indicates December 31, 9999. Formatted as YYYYMMDD.
@active_start_time = '130600', --HHMMSS |
The time on any day between active_start_date and active_end_date to begin execution of a job. active_start_timeis int, with a default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock, and must be entered using the form HHMMSS.
@owner_login_name = NULL, --owned by creator |
The name of the server principal that owns the schedule. owner_login_name is sysname, with a default of NULL, which indicates that the schedule is owned by the creator.
@schedule_uid = @ScheduleUID OUTPUT, @schedule_id = @ScheduleId OUTPUT |
EXEC sp_attach_schedule @job_id = @JobId, @schedule_id = @ScheduleId |
EXEC sp_add_jobserver @job_id = @JobId, @server_name = @ServerName |
EXEC sp_delete_job @job_name = N'Job Name' |
Either job_id or job_namemust be specified; both cannot be specified.
Here you can find more datails about rest of the fields from system stored procedures.
A real example from our projects:
USE msdb DECLARE @JobId uniqueidentifier DECLARE @ServerName VARCHAR(200) DECLARE @DBName VARCHAR(200) SELECT @ServerName = 'your server name' SELECT @DBName = N'your database name' EXEC sp_add_job @job_name = N'your job name', @enabled = 1, @description = N'your job description', @owner_login_name = 'sa', --should be left like this to not have problems with user rights @job_id = @jobid OUTPUT EXEC sp_add_jobstep @job_id = @JobId, @step_id = 1, @step_name = N'SQL Job Step 1', @subsystem = N'TSQL', @command = N'Exe', @on_success_action = 1, --Quit with success @on_fail_action = 2, --Quit with failure @database_name = @DBName, @database_user_name = NULL -- job owner's user context EXEC sp_add_schedule @schedule_name = N'your schedule', @enabled = 1, @freq_type = 4, --daily @freq_interval = 1, --every day @freq_subday_type = 0x8, --hours @freq_subday_interval = 1, --each hour @active_start_date = '20110610', --YYYYMMDD @active_start_time = '130600', --HHMMSS @owner_login_name = NULL, --owned by creator @schedule_uid = @ScheduleUID OUTPUT, @schedule_id = @ScheduleId OUTPUT EXEC sp_attach_schedule @job_id = @JobId, @schedule_id = @ScheduleId EXEC sp_add_jobserver @job_id = @JobId, @server_name = @ServerName --EXEC sp_delete_job @job_name = N'your sql job' |
Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.