sp_help_jobserver (Transact-SQL)
Applies to:
SQL Server
Returns information about the server for a given job.
Transact-SQL syntax conventions
Syntax
sp_help_jobserver
[ [ @job_id = ] 'job_id' ]
[ , [ @job_name = ] N'job_name' ]
[ , [ @show_last_run_details = ] show_last_run_details ]
[ ; ]
Arguments
[ @job_id = ] 'job_id'
The job identification number for which to return information. @job_id is uniqueidentifier, with a default of NULL
.
Either @job_id or @job_name must be specified, but both can't be specified.
[ @job_name = ] N'job_name'
The job name for which to return information. @job_name is sysname, with a default of NULL
.
Either @job_id or @job_name must be specified, but both can't be specified.
[ @show_last_run_details = ] show_last_run_details
Whether the last-run execution information is part of the result set. @show_last_run_details is tinyint, with a default of 0
.
0
doesn't include last-run information.1
includes last-run information.
Return code values
0
(success) or 1
(failure).
Result set
Column name | Data type | Description |
---|---|---|
server_id |
int | Identification number of the target server. |
server_name |
nvarchar(30) | Computer name of the target server. |
enlist_date |
datetime | Date the target server enlisted into the master server. |
last_poll_date |
datetime | Date the target server last polled the master server. |
If sp_help_jobserver
is executed with @show_last_run_details set to 1
, the result set has these extra columns.
Column name | Data type | Description |
---|---|---|
last_run_date |
int | Date the job last started execution on this target server. |
last_run_time |
int | Time the job last started execution on this server. |
last_run_duration |
int | Duration of the job the last time it ran on this target server (in seconds). |
last_outcome_message |
nvarchar(1024) | Describes the last outcome of the job. |
last_run_outcome |
int | Outcome of the job the last time it ran on this server:0 = Failed1 = Succeeded3 = Canceled5 = Unknown |
Permissions
This stored procedure is owned by the db_owner role. You can grant EXECUTE
permissions for any user, but these permissions may be overridden during a SQL Server upgrade.
Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb
database:
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Members of SQLAgentUserRole can only view information for jobs that they own.
Examples
The following example returns information, including last-run information, about the NightlyBackups
job.
USE msdb;
GO
EXEC dbo.sp_help_jobserver
@job_name = N'NightlyBackups',
@show_last_run_details = 1;
GO