sp_help_job

sp_help_job#

Returns information about jobs.

Syntax#

sp_help_job { [ @job_id = ] job_id
   [ @job_name = ] 'job_name' }
     [ , [ @job_aspect = ] 'job_aspect' ]        /* not supported */
     [ , [ @job_type = ] 'job_type' ]            /* not supported */
     [ , [ @owner_login_name = ] 'login_name' ]
     [ , [ @subsystem = ] 'subsystem' ]          /* not supported */
     [ , [ @category_name = ] 'category' ]       /* not supported */
     [ , [ @enabled = ] enabled ]
     [ , [ @execution_status = ] status ]        /* not supported */
     [ , [ @date_comparator = ] 'date_comparison' ]  /* not supported */
     [ , [ @date_created = ] date_created ]          /* not supported */
     [ , [ @date_last_modified = ] date_modified ]   /* not supported */
     [ , [ @description = ] 'description_pattern' ]  /* not supported */

Arguments#

[ @job_id = ] ‘job_id’

The job identification number. The job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] ‘job_name’

The name of the job. The name must be unique. job_name is sysname, with a default of NULL.

Note

To view a specific job, either job_id or job_name must be specified. Omit both job_id and job_name to return information about all jobs.

[ @job_aspect = ] ‘job_aspect’ – not supported

The job attribute to display. _job_aspect_ is varchar(9), with a default of NULL. The parameter is not supported.

[ @job_type = ] ‘job_type’ – not supported

The type of jobs to include in the report. _job_type_ is varchar(12), with a default of NULL. The parameter is not supported.

[ @owner_login_name = ] ‘login_name’ ]

The login name of the owner of the job. _login_name_ is sysname, with a default of NULL.

[ @subsystem = ] ‘subsystem’ – not supported

The name of the subsystem. __subsystem__ is nvarchar(40), with a default of NULL. The parameter is not supported.

[ @category_name = ] ‘category’ – not supported

The name of the category. category is sysname, with a default of NULL. The parameter is not supported.

[ @enabled = ] enabled ]

A number indicating whether information is shown for enabled jobs or disabled jobs. enabled is tinyint, with a default of NULL. 1 indicates enabled jobs, and 0 indicates disabled jobs.

[ @execution_status = ] status – not supported

The execution status for the jobs. _status_ is int, with a default of NULL. The parameter is not supported.

[ @date_comparator = ] ‘date_comparison’ – not supported

The comparison operator to use in comparisons of _date_created_ and _date_modified_. The parameter is not supported.

[ @date_created = ] date_created – not supported

The date the job was created. _date_created_ is datetime, with a default of NULL. The parameter is not supported.

[ @date_last_modified = ] date_modified – not supported

The date the job was last modified. _date_modified_ is datetime, with a default of NULL. The parameter is not supported.

[ @description = ] ‘description_pattern’ – not supported

The description of the job. _description_pattern_ is nvarchar(512), with a default of NULL. The parameter is not supported.

Result Sets#

If no arguments are specified, sp_help_job returns this result set.

Column name

Data type

Description

job_id

uniqueidentifier

Unique ID of the job.

originating_server

nvarchar(30)

Not supported.

name

sysname

Name of the job.

enabled

tinyint

Indicates whether the job is enabled to be executed.

description

nvarchar(512)

Description for the job.

start_step_id

int

ID of the step in the job where execution should begin.

category

sysname

Not supported.

owner

sysname

Job owner.

notify_level_eventlog

int

Not supported.

notify_level_email

int

Not supported.

notify_level_netsend

int

Not supported.

notify_level_page

int

Not supported.

notify_email_operator

sysname

Not supported.

notify_netsend_operator

sysname

Not supported.

notify_page_operator

sysname

Not supported.

delete_level

int

Bitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are: 0 = Never, 1 = When a job succeeds, 2 = When the job fails, 3 = Whenever the job completes (regardless of the job outcome)

date_created

datetime

Date the job was created.

date_modified

datetime

Date the job was last modified.

version_number

int

Version of the job (automatically updated each time the job is modified).

last_run_date

int

Date the job last started execution.

last_run_time

int

Time the job last started execution.

last_run_outcome

int

Outcome of the job the last time it ran: 0 = Failed, 1 = Succeeded, 3 = Canceled, 5 = Unknown

next_run_date

int

Date the job is scheduled to run next.

next_run_time

int

Time the job is scheduled to run next.

next_run_schedule_id

int

Not supported.

current_execution_status

int

Current execution status.

current_execution_step

sysname

Current execution step in the job.

current_retry_attempt

int

If the job is running and the step has been retried, this is the current retry attempt.

has_step

int

Number of job steps the job has.

has_schedule

int

Number of job schedules the job has.

has_target

int

Not supported.

type

int

Not supported.

Permissions#

By default, members of the sysadmin fixed server role can execute this stored procedure.

Examples#

  1. List information for all jobs

The following example executes the sp_help_job procedure with no parameters to return the information for all of the jobs currently defined in the system.

EXEC dbo.sp_help_job ;

See Also#