Jobs Wizard (SQL Server)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Object Wizards

This wizard lets you create SQL Agent jobs. It lets you specify a category, set up associated notifications, create a step structure, set up schedules, work with associated alerts, and define a single or multiple server targets for the job.

Note: For an overview of SQL Agent service support, see SQL Server Agent.

To create a new job using a wizard:

  1. Open an object creation wizard for a job. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

For information on related SQL Agent elements, see the following topics:

Jobs (SQL Server) - Properties

When creating or editing a SQL Agent job, this tab panel provides most settings of an sp_add_job call, letting you specify an enabled/disabled status and select a category, as well as specify notify and delete levels for the job. This tab has the following settings:

Category Setting

Creation

Owner and Name

Let you provide basic identification details.

Category

Lets you select the category to which the job belongs.

Description

Lets you provide a textual description for the job.

Enabled

Lets you specify whether the job is enabled or disabled.

Notifications

Each subcategory lets you specify when notifications should be sent or jobs deleted (NEVER, WHEN JOB SUCCEEDS, WHEN JOB FAILS, or WHEN JOB COMPLETES).

The Email, Page, and Net send subcategories let you select the specific operator to which notifications are directed. For information on creating operators, see Operators Wizard (SQL Server).

The Windows application event log subcategory lets you select when an entry is placed in the Microsoft Windows application log for this job.

The Job Deletion subcategory lets you select when a job is deleted.

Jobs (SQL Server) - Steps

When creating or editing a job, this tab/panel lets you work with the steps for the job. You can use it to create one or more sp_add_jobstep calls that will be included as part of the job definition DDL.

To add a step to the job

  1. Click the Add Step button, provide a Name for the step and press TAB or ENTER. The step is added to the step list with a set of default properties.
  2. Modify the default properties using the table below as a guide.
    Note: Properties available differ by the subsystem Type you select.
Setting Description

Is start step

Lets you designate this step as the first step in the job. Note that at least one step must have this check box selected. If you deselect this check box, the change does not actually take effect until you select the check box in another step.

Type

Lets you select the subsystem used by the SQL Server Agent service to execute the step: ACTIVESCRIPTING, CMDEXEC, DISTRIBUTION, SNAPSHOT, LOGREADER, MERGE, QUEUEREADER, ANALYSISQUERY, 'ANALYSISCOMMAND, DTS, POWERSHELL (SQL Server 2008 only), or TSQL.

Database

The name of the database in which to execute the step.

Database user name

The user account to use when executing the step.

Command

The commands to be executed by the SQLServerAgent service through subsystem.

On success action

The job action to perform (QUIT WITH SUCCESS, QUIT WITH FAILURE, GO TO NEXT STEP, GO TO STEP: [N] STEPNAME) if the step succeeds.

Retry attempts

The number of retry attempts to use if the step fails.

Retry interval

The duration, in minutes, between retry attempts.

On fail action

The job action to perform (QUIT WITH SUCCESS, QUIT WITH FAILURE, GO TO NEXT STEP, GO TO STEP: [N] STEPNAME) if the step fails.

Output file

The name of the file in which the output of this step is saved

Append output to file, Log to table (overwrite), Log to table (append), and Output to history

Lets you set the flags that control how the output file is written.

To modify a job step

  1. Select a step from the step list, and modify properties accordingly.

To delete a step from the job

  1. Select a step from the step list and click Delete. If you delete the step that currently has the Is start step check box selected, the next lower step in the list becomes the first step. If there was no step below the deleted step, the first step in the list has its Is start step check box selected.

To change the order for a step

  1. Select a step from the step list, and use the arrow buttons to move the step up or down one position.

Jobs (SQL Server) - Schedules

When creating or editing a job, this tab/panel lets you manage the schedules for a job. Specifically, you can:

  • Create new schedules for this job
  • Add schedules that were defined in other job definitions
  • Delete schedules currently associated wit the current job definition
  • Display all jobs using a selected schedule

To add a schedule

  1. Click New, provide a Name for the schedule and press TAB or ENTER. DBArtisan adds the schedule to the schedule list with a set of default properties.
  2. Select a frequency Type: START AUTOMATICALLY WHEN SQL SERVER AGENT STARTS, START WHENEVER THE CPUS BECOME IDLE, ONE TIME, DAILY, WEEKLY, MONTHLY, or MONTHLY RELATIVE TO FREQUENCY INTERVAL.
  3. Use the Enabled check box to enable or disable the schedule.
  4. If you selected a Type of ONE TIME ONLY, use the One-time occurrence controls to provide the Date and Time of the one-tome occurrence.
  5. If you selected a Type of DAILY, WEEKLY, MONTHLY, or MONTHLY RELATIVE TO FREQUENCY INTERVAL, use the table below as a guide to providing details in the Frequency, Daily Frequency, and Duration property groups.
Property group Type Description

Frequency

Daily

Lets you specify the recurrence frequency, in days.

Weekly

Lets you specify the recurrence frequency, in weeks., and the specific weekdays on which the job is to execute.

Monthly

Lets you specify the recurrence frequency, in months, and the specific day of the month on which the job will execute.

Monthly relative to frequency interval

Lets you specify the recurrence frequency, in months, and a relative weekday (such as the second Sunday) on which the job is to execute.

Daily Frequency

Daily

For the specific days on which the job is to execute, this group lets you specify whether the job is to execute once per day or several times per day. If you select the Occurs once check box, use the at control to specify the schedule time. If you deselect the Occurs once check box, use the remaining controls to specify an hourly recurrence frequency, and a start and end time within the day when the schedule is active.

Duration

Use these controls to specify the start and end dates for a fixed duration schedule. For an open-ended schedule, provide a start date and select the No end date check box.

To delete a schedule from the schedule list

  1. Select a schedule from the schedule list and click Remove.

To copy an already existing schedule definition to the current job

  1. Click Pick. A Pick schedule for job dialog opens. The Available schedules list shows all schedules defined on this datasource.
  2. Select the schedule that is to be added to the schedule list for this job definition and click OK.

To display all jobs currently using a schedule in the schedule list

  1. Select a schedule from the schedule list and click Jobs in schedule. A Jobs in schedule dialog opens, showing all jobs currently using the selected schedule.

Jobs (SQL Server) - Alerts

When creating or editing a job, this tab/panel provides a convenient way to work with SQL Agent alerts associated with this job. On opening, this tab provides a list of all alerts that specify this job as the target job to be executed in response to those alerts. When you execute the job that creates or edits this job, DDL to create all alerts on this tab is submitted.

Note: Alerts cannot be created for multi-target jobs. They will be rejected by the server.

Manage the alerts list as follows:

  • Click Add to create a new alert to be added to the list. This opens the Alert Wizard, with the Execute Job check box selected and the Job Name box specifying the current job, both uneditable. For information on using the Alert Wizard, see Alerts Wizard (SQL Server).
  • Select an alert and click Edit to open an editor on the selected alert. The Execute Job check box and the Job Name box specifying the current job are both uneditable. For information on using the Alert Wizard, see Alerts Editor (SQL Server).
  • Select an alert and click Remove to drop the selected alert from the alert list. DDL to create or alter the alert is not submitted when you execute the job that creates or edits this job. For existing alert definitions, this does not delete the definition nor does it modify the Execute Job or Job Name controls.

Jobs (SQL Server) - Target

When creating or editing a job, this tab/panel lets you specify a target server or servers for the job. The default setting, Target Local Server, specifies that the job will be executed on the local server. The Target Multiple Servers setting is only available in multiserver operation domains, with the selectable server names populated from the systargetservers table.

Note: Alerts cannot be created for multi-target jobs. They will be rejected by the server.

To target a job at one or more servers in a multiple server environment

  1. Select Target Multiple Servers.
  2. Select the check box associated with each server on which the job will be executed.