Use the Execute SQL Server Agent Job Task dialog to execute Microsoft SQL Server Agent jobs within a maintenance plan. This option will not be available if you have no SQL Server Agent jobs on the selected connection.
This task uses the .sp_start_job statement.

Connection

Select the server connection to use when performing this task.

New

Create a new server connection to use when performing this task. The New Connection dialog box is described below.

Available SQL Agent jobs

Select the job to execute. The grid provides the Job name and Description to identify the jobs.

View T-SQL

View the Transact-SQL statements performed against the server for this task, based on the selected options.

New Connection Dialog Box

Connection name

Enter a name for the new connection.

Select or enter a server name

Select a server to connect to when performing this task.

Refresh

Refresh the list of available servers.

Enter information to log on to the server

Specify how to authenticate against the server.

Use Windows integrated security

Connect to an instance of the SQL Server Database Engine with Microsoft Windows Authentication.

Use a specific user name and password

Connect to an instance of the SQL Server Database Engine using SQL Server Authentication. This option is not available.

User name

Provide a SQL Server login to use when authenticating. This option is not available.

Password

Provide a password to use when authenticating. This option is not available.

To create a SQL Server Agent job

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.

Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.

sp_add_job (Transact-SQL)

Syntax

sp_add_job [ @job_name = ] 'job_name'
     [ , [ @enabled = ] enabled ] 
     [ , [ @description = ] 'description' ] 
     [ , [ @start_step_id = ] step_id ] 
     [ , [ @category_name = ] 'category' ] 
     [ , [ @category_id = ] category_id ] 
     [ , [ @owner_login_name = ] 'login' ] 
     [ , [ @notify_level_eventlog = ] eventlog_level ] 
     [ , [ @notify_level_email = ] email_level ] 
     [ , [ @notify_level_netsend = ] netsend_level ] 
     [ , [ @notify_level_page = ] page_level ] 
     [ , [ @notify_email_operator_name = ] 'email_name' ] 
          [ , [ @notify_netsend_operator_name = ] 'netsend_name' ] 
     [ , [ @notify_page_operator_name = ] 'page_name' ] 
     [ , [ @delete_level = ] delete_level ] 
     [ , [ @job_id = ] job_id OUTPUT ]

Arguments




[ @job_name = ] 'job_name'
 The name of the job. The name must be unique and cannot contain the percent (%) character. job_nameis nvarchar(128), with no default.
[ @enabled = ] enabled
Indicates the status of the added job. enabledis 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 = ] 'description'
The description of the job. description is nvarchar(512), with a default of NULL. If description is omitted, "No description available" is used.
[ @start_step_id = ] step_id
The identification number of the first step to execute for the job. step_idis int, with a default of 1.
[ @category_name = ] 'category'
The category for the job. categoryis sysname, with a default of NULL.
[ @category_id = ] category_id
A language-independent mechanism for specifying a job category. category_idis int, with a default of NULL.
[ @owner_login_name = ] 'login'
The name of the login that owns the job. loginis sysname, with a default of NULL, which is interpreted as the current login name. Only members of thesysadmin fixed server role can set or change the value for @owner_login_name. If users who are not members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
[ @notify_level_eventlog = ] eventlog_level
A value indicating when to place an entry in the Microsoft Windows application log for this job. eventlog_levelis int, and can be one of these values.  




Value Description
0 Never
1 On success
2 (default) On failure
3 Always
 

[ @notify_level_email = ] email_level
A value that indicates when to send an e-mail upon the completion of this job. email_levelis int, with a default of 0, which indicates never. email_leveluses the same values as eventlog_level.
[ @notify_level_netsend = ] netsend_level
A value that indicates when to send a network message upon the completion of this job. netsend_levelis int, with a default of 0, which indicates never.netsend_level uses the same values as eventlog_level.
[ @notify_level_page = ] page_level
A value that indicates when to send a page upon the completion of this job. page_levelis int, with a default of 0, which indicates never. page_leveluses the same values as eventlog_level.
[ @notify_email_operator_name = ] 'email_name'
The e-mail name of the person to send e-mail to when email_level is reached. email_name is sysname, with a default of NULL.
[ @notify_netsend_operator_name = ] 'netsend_name'
The name of the operator to whom the network message is sent upon completion of this job. netsend_nameis sysname, with a default of NULL.
[ @notify_page_operator_name = ] 'page_name'
The name of the person to page upon completion of this job. page_nameis sysname, with a default of NULL.
[ @delete_level = ] delete_level
A value that indicates when to delete the job. delete_valueis int, with a default of 0, which means never. delete_leveluses the same values as eventlog_level.  [ @job_id = ] job_idOUTPUT
The job identification number assigned to the job if created successfully. job_idis an output variable of type uniqueidentifier, with a default of NULL.
Return Code Values 
 
0 (success) or 1 (failure)
Result Sets 
 
None
Remarks 
 
@originating_server exists in sp_add_job, but is not listed under Arguments. @originating_server is reserved for internal use.
After sp_add_job has been executed to add a job, sp_add_jobstep can be used to add steps that perform the activities for the job. sp_add_jobschedule can be used to create the schedule that the SQL Server Agent service uses to execute the job. Use sp_add_jobserver to set the SQL Server instance where the job executes, and sp_delete_jobserver to remove the job from the SQL Server instance.
If the job will execute on one or more target servers in a multiserver environment, use sp_apply_job_to_targets to set the target servers or target server groups for the job. To remove jobs from target servers or target server groups, use sp_remove_job_from_targets.
SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
Permissions 
 
To run this stored procedure, users must be a member of the sysadmin fixed server role, or be granted one of the following SQL Server Agent fixed database roles, which reside in the msdb database:
SQLAgentUserRole

SQLAgentReaderRole
SQLAgentOperatorRole
For information about the specific permissions that are associated with each of these fixed database roles, see SQL Server Agent Fixed Database Roles.
Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who are not members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
Examples 
 
A. Adding a job
This example adds a new job named NightlyBackups.  
 
USE msdb ;
GO

EXEC dbo.sp_add_job
    @job_name = N'NightlyBackups' ;
GO
B. Adding a job with pager, e-mail, and net send information
This example creates a job named Ad hoc Sales Data Backup that notifies François Ajenstat (by pager, e-mail, or network pop-up message) if the job fails, and deletes the job upon successful completion.   
USE msdb ;
GO

EXEC dbo.sp_add_job
    @job_name = N'Ad hoc Sales Data Backup', 
    @enabled = 1,
    @description = N'Ad hoc backup of sales data',
    @owner_login_name = N'françoisa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_level_netsend = 2,
    @notify_level_page = 2,
    @notify_email_operator_name = N'François Ajenstat',
    @notify_netsend_operator_name = N'François Ajenstat', 
    @notify_page_operator_name = N'François Ajenstat',
    @delete_level = 1 ;
GO

sp_start_job (Transact-SQL)

sp_start_job 
     {   [@job_name =] 'job_name'
       | [@job_id =] job_id }
     [ , [@error_flag =] error_flag]
     [ , [@server_name =] 'server_name']
     [ , [@step_name =] 'step_name']
     [ , [@output_flag =] output_flag]

Arguments

[ @job_name= ] 'job_name': The name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.
[ @job_id= ] job_id: The identification number of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_id is uniqueidentifier, with a default of NULL.
[ @error_flag= ] error_flag: Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
[ @server_name= ] 'server_name': The target server on which to start the job. server_name is nvarchar(128), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.
[ @step_name= ] 'step_name': The name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL
[ @output_flag= ] output_flag: Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

This stored procedure is in the msdb database.

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. 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 and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.

Examples

The following example starts a job named Weekly Sales Data Backup.

USE msdb ;
GO

EXEC dbo.sp_start_job N'Weekly Sales Data Backup' ;
GO

sp_delete_job (Transact-SQL)




sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ,
     [ , [ @originating_server = ] 'server' ] 
     [ , [ @delete_history = ] delete_history ]
     [ , [ @delete_unused_schedule = ] delete_unused_schedule ]
Arguments



[ @job_id= ] job_id
Is the identification number of the job to be deleted. job_id is uniqueidentifier, with a default of NULL.
[ @job_name= ] 'job_name'
Is the name of the job to be deleted. job_name is sysname, with a default of NULL.  [ @originating_server= ] 'server'
For internal use.
[ @delete_history= ] delete_history
Specifies whether to delete the history for the job. delete_history is bit, with a default of 1. When delete_history is 1, the job history for the job is deleted. When delete_history is 0, the job history is not deleted. Note that when a job is deleted and the history is not deleted, historical information for the job will not display in the SQL Server Agent graphical user interface job history, but the information will still reside in the sysjobhistory table in the msdb database.
[ @delete_unused_schedule= ] delete_unused_schedule
Specifies whether to delete the schedules attached to this job if they are not attached to any other job. delete_unused_schedule is bit, with a default of 1. When delete_unused_schedule is 1, schedules attached to this job are deleted if no other jobs reference the schedule. When delete_unused_schedule is 0, the schedules are not deleted.
Return Code Values



0 (success) or 1 (failure)
Result Sets



None
Remarks



The @originating_server argument is reserved for internal use.
The @delete_unused_schedule argument provides backward compatibility with previous versions of SQL Server by automatically removing schedules that are not attached to any job. Notice that this parameter defaults to the backward-compatible behavior. To retain schedules that are not attached to a job, you must provide the value 0 as the @delete_unused_schedule argument.
SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
This stored procedure cannot delete maintenance plans, and cannot delete jobs that are part of maintenance plans. Instead, use SQL Server Management Studio to delete maintenance plans.
Permissions



By default, members of the sysadmin fixed server role can execute this stored procedure. 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 the sysadmin fixed server role can execute sp_delete_job to delete any job. A user that is not a member of the sysadmin fixed server role can only delete jobs owned by that user.
Examples

The following example deletes the job NightlyBackups.
USE msdb ;
GO

EXEC sp_delete_job
    @job_name = N'NightlyBackups' ;
GO
sp_update_job (Transact-SQL)




sp_update_job [ @job_id =] job_id | [@job_name =] 'job_name'
     [, [@new_name =] 'new_name' ] 
     [, [@enabled =] enabled ]
     [, [@description =] 'description' ] 
     [, [@start_step_id =] step_id ]
     [, [@category_name =] 'category' ] 
     [, [@owner_login_name =] 'login' ]
     [, [@notify_level_eventlog =] eventlog_level ]
     [, [@notify_level_email =] email_level ]
     [, [@notify_level_netsend =] netsend_level ]
     [, [@notify_level_page =] page_level ]
     [, [@notify_email_operator_name =] 'email_name' ]
     [, [@notify_netsend_operator_name =] 'netsend_operator' ]
     [, [@notify_page_operator_name =] 'page_operator' ]
     [, [@delete_level =] delete_level ] 
     [, [@automatic_post =] automatic_post ]
Arguments


[ @job_id =] job_id
The identification number of the job to be updated. job_idis uniqueidentifier.
[ @job_name =] 'job_name'
The name of the job. job_nameis nvarchar(128).  [ @new_name =] 'new_name'
The new name for the job. new_nameis nvarchar(128).
[ @enabled =] enabled
Specifies whether the job is enabled (1) or not enabled (0). enabledis tinyint.
[ @description =] 'description'
The description of the job. description is nvarchar(512).
[ @start_step_id =] step_id
The identification number of the first step to execute for the job. step_idis int.
[ @category_name =] 'category'
The category of the job. categoryis nvarchar(128).
[ @owner_login_name =] 'login'
The name of the login that owns the job. loginis nvarchar(128) Only members of the sysadmin fixed server role can change job ownership.
[ @notify_level_eventlog =] eventlog_level
Specifies when to place an entry in the Microsoft Windows application log for this job. eventlog_levelis int, and can be one of these values.  

Value Description (action)
0 Never
1 On success
2 On failure
3 Always

[ @notify_level_email =] email_level
Specifies when to send an e-mail upon the completion of this job. email_levelis int. email_leveluses the same values as eventlog_level.
[ @notify_level_netsend =] netsend_level
Specifies when to send a network message upon the completion of this job. netsend_levelis int. netsend_leveluses the same values as eventlog_level.
[ @notify_level_page =] page_level
Specifies when to send a page upon the completion of this job. page_levelis int. page_leveluses the same values as eventlog_level.
[ @notify_email_operator_name =] 'email_name'
The e-mail name of the operator to whom the e-mail is sent when email_level is reached. email_name is nvarchar(128).
[ @notify_netsend_operator_name =] 'netsend_operator'
The name of the operator to whom the network message is sent. netsend_operator is nvarchar(128).
[ @notify_page_operator_name =] 'page_operator'
The name of the operator to whom a page is sent. page_operator is nvarchar(128).
[ @delete_level =] delete_level
Specifies when to delete the job. delete_valueis int. delete_leveluses the same values as eventlog_level.
[ @automatic_post =] automatic_post
Reserved.
Return Code Values



0 (success) or 1 (failure)
Remarks



sp_update_job must be run from the msdb database.
sp_update_job changes only those settings for which parameter values are supplied. If a parameter is omitted, the current setting is retained.
Permissions



By default, members of the sysadmin fixed server role can execute this stored procedure. 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.
Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.
Examples



The following example changes the name, description, and enabled status of the job NightlyBackups.


USE msdb ;
GO

EXEC dbo.sp_update_job
    @job_name = N'NightlyBackups',
    @new_name = N'NightlyBackups -- Disabled',
    @description = N'Nightly backups disabled during server migration.',
    @enabled = 0 ;
GO

Prashant Mishra

Total Pageviews

Tuesday, May 3, 2011

Execute SQL Server Agent Job Task (Maintenance Plan)

To create a SQL Server Agent job

sp_add_job (Transact-SQL)

A. Adding a job

B. Adding a job with pager, e-mail, and net send information

sp_start_job (Transact-SQL)

sp_delete_job (Transact-SQL)

sp_update_job (Transact-SQL)

No comments:

Post a Comment