作业相关操作

USE msdb

GO

--禁用JOB

DECLARE

    @enable_or_disable BIT ,

    @job_name SYSNAME,

    @job_id UNIQUEIDENTIFIER

SELECT

    @enable_or_disable = 0, --1 启用，0 禁用

    @job_name = 'Job_Name'  --作业名称

--得到job_id

EXEC dbo.sp_verify_job_identifiers '@job_name' , '@job_id' , @job_name OUTPUT , @job_id

EXEC dbo.sp_update_job

    @job_name = @job_name,

    @enabled = @enable_or_disable

EXEC dbo.sp_update_job

    @job_id,

    @enabled = @enable_or_disable

--删除JOB

/*

IF EXISTS( SELECT TOP (1) 1 FROM dbo.sysjobs WITH (NOLOCK) WHERE [ name ] = @job_name)

    EXEC dbo.sp_delete_job @job_name = @job_name

*/

--开始JOB

/*

IF EXISTS( SELECT TOP (1) 1 FROM dbo.sysjobs WITH (NOLOCK) WHERE [ name ] = @job_name)

    EXEC dbo.sp_start_job @job_name = @job_name

*/

--停止JOB

IF EXISTS( SELECT TOP (1) 1 FROM dbo.sysjobs WITH (NOLOCK) WHERE [ name ] = @job_name)

    EXEC dbo.sp_stop_job @job_name = @job_name

---示例

--如果存在“hq2定时作业”，则禁用并停止,ljy 20120327增加

IF EXISTS( SELECT TOP (1) 1 FROM msdb.dbo.sysjobs WITH (NOLOCK) WHERE [ name ] = 'hq2定时作业' and [enabled] = 1 )

BEGIN

     EXEC msdb.dbo.sp_update_job @job_name = 'hq2定时作业' ,@enabled = 0

    

     --如果正在执行,则停止掉作业

     create table #job_run_status

        (

           job_id                  uniqueidentifier  not null ,

           last_run_date           int               not null ,

           last_run_time           int               not null ,

           next_run_date           int               not null ,

           next_run_time           int               not null ,

           next_run_schedule_id    int               not null ,

           requested_to_run        int               not null , -- bool

           request_source          int               not null ,

           request_source_id       sysname           collate database_default null ,

           running                 int               not null , -- bool

           current_step            int               not null ,

           current_retry_attempt   int               not null ,

           job_state               int               not null

        )

    -- 2. 得到作业的状态

    insert into #job_run_status

    execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

    -- 3. 得到正在运行的作业,并且结束掉

    if exists ( select 1 from #job_run_status s

              inner join msdb.dbo.sysjobs j on s.job_id = j.job_id

     where s.running = 1 AND j. name = 'hq2定时作业' )

     BEGIN

        --对正在运行的作业进行停止

        EXEC msdb.dbo.sp_stop_job @job_name = 'hq2定时作业'

     END

END