sp_addtask SQL Executive Stored Procedure

For SQL Server 6.5 information, see sp_addtask in What's New for SQL Server 6.5.

Creates a scheduled task.

Syntax

sp_addtask name [, subsystem] [, server] [, username] [, databasename]
    [, enabled] [, freqtype] [, freqinterval] [, freqsubtype] [, freqsubinterval]
    [, freqrelativeinterval] [, freqrecurrencefactor] [, activestartdate]
    [, activeenddate] [, activestarttimeofday] [, activeendtimeofday]
    [, nextrundate] [, nextruntime] [, runpriority] [, emailoperatorname]
    [, retryattempts] [, retrydelay] [, command] [, loghistcompletionlevel]
    [, emailcompletionlevel] [, description] [, tagadditionalinfo] [, tagobjectid]
    [, tagobjecttype] [, @newid OUTPUT]

where

name: Is the name of the task being scheduled.
subsystem: Specifies the subsystem to use. Can be Distribution, LogReader, Sync, TSQL, or CmdExec. These must be typed using the capitalization (case) shown.
server: Specifies the server on which to execute this task.
username: Specifies the database user to impersonate when executing this task. Only the system administrator or the database owner can specify this parameter.
databasename: Specifies the database to use when executing the task.
enabled: Specifies the status of scheduled tasks: 1 indicates enabled, the tasks will be scheduled and run; 0 indicates disabled, the tasks will not be scheduled and run.
freqtype: Specifies the frequency for this task:
1One Time
2On Demand (the default)
4Daily
8Weekly
16Monthly
32Monthly Relative
64Autostart
124Recurring
freqinterval: Specifies the interval for freqtype.
freqsubtype: Specifies how often to reschedule the task during the defined period:
1 Once (the default)
2 Second
4 Minute
8 Hour
freqsubinterval: Specifies the value for freqsubtype when the freqsubtype is set to second, minute, or hour.
freqrelativeinterval: Specifies the value used when freqtype is set to 32 (monthly relative). Indicates the first, second, third, fourth, or last day of the month:
1First (the default)
2Second
4Third
8Fourth
16Last
freqrecurrencefactor: Specifies how often the task should reoccur during the defined freqtype.
activestartdate: Specifies the date when this task will first be scheduled. Formatted as YYYYMMDD. The default is 0.
activeenddate: Specifies the date when this task will stop being scheduled. Formatted as YYYYMMDD. The default is 0.
activestarttimeofday: Specifies when this task will be scheduled initially. Formatted as HHMMSS. The default is 0.
activeendtimeofday: Specifies when this task will stop being scheduled. Formatted as HHMMSS. The default is 0.
nextrundate: Specifies the date the task will be scheduled. If the current date is greater than activestartdate, specifies the next date that the task will be scheduled; 0 indicates that the SQL Executive will calculate the next appropriate date. Formatted as YYYYMMDD. The default is 0.
nextruntime: Specifies the date the task will be scheduled if the current time is greater than the activestarttime. 0 indicates that the SQL Executive will calculate the next appropriate time. Formatted as HHMMSS. The default is 0.
runpriority: Specifies the thread priority level at which the subsystem event thread runs. The default is 0.
emailoperatorname: The e-mail name of the operator who will receive notifications. The default is NULL.
retryattempts: Specifies the number of times the SQL Executive retries before issuing an error. The default is 0.
retrydelay: Specifies the interval the SQL Executive waits before retrying the task. The default is 1.
command: Specifies a command string (based on the valid specified subsystem commands) to be passed to the subsystem. If the specified subsystem is TSQL, the command cannot consist of more than one batch. The batch separator GO is not supported. The default is NULL.
loghistcompletionlevel: Specifies the level of events to capture in the event log: 1 captures all successfully run events, 2 captures only failed events.
emailcompletionlevel: Specifies the level of events to capture via e-mail: 1 captures all successfully run events; 2 captures only failed events. The default is 2.
description: Is a description of the task.
tagadditionalinfo: Is user-defined information. This parameter is not required and is normally not used. Varchar (96).
tagobjectid: Is user-defined information. This parameter is not required and is normally not used. Integer.
tagobjecttype: Is user-defined information. This parameter is not required and is normally not used. Integer.
@newid OUTPUT: Is an output parameter that returns the system generated ID for the task.

Remarks

This stored procedure must be run from the msdb database.

Examples

A. Add a Sync Task

This example adds a task for "auth_table_sync" in subsystem "Sync" to run each hour from 9 A.M. to 11 P.M. If scheduled to run after September 26, 1995, the task starts at 4:00 P.M. the first day.

sp_addtask "auth_table_sync", "Sync", "server1", "sa",
    "pubs", 1, 4, 1, 8, 1, 0, 0, 19950926, 19991231, 
    090000, 230000, 00000000, 160000, 0, "", 2, 1, "", 2, 2

B. Add a Distribution Task

This example adds a task for "dist_server2_pubs" in subsystem "Distribution" to run every 15 minutes and pass the command string "-Sserver2 -Usa -P -dpubs -pserver1".

sp_addtask "dist_server2_pubs", "Distribution", "server1", 
    "sa", "pubs", 1, 4, 1, 4, 15, 0, 0, 19950926, 19991231, 000500, 
    235500, 00000000, 000000, 0, "", 2, 1, 
    "-server2 -Usa -P -dpubs -pserver1", 2, 2

Permission

Execute permission defaults to the public group.

Tables Used

systasks


sp_droptask	sp_purgehistory
sp_helphistory	sp_updatetask
sp_helptask