【xp_sqlagent_enum_jobs】取得SQL Server 作业执行状态的存储过程。

xp_sqlagent_enum_jobs是微软未公开的存储过程，用于取得作业的相关信息，在自己进行调度处理多作业时，用它来判断作业的执行情况再合适不过了。

create   table  #help_job(
    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  NULL ,
    running  INT   NOT   NULL ,  --  BOOL
    current_step  INT   NOT   NULL ,
    current_retry_attempt  INT   NOT   NULL ,
    job_state  INT   NOT   NULL
)

-- 判断作业是否正在运行
     while   @job_run_flag   =   1
     begin
         -- 删除临时表信息
         delete  #help_job
         -- 添加信息
         INSERT   INTO  #help_job
         EXECUTE  master.dbo.xp_sqlagent_enum_jobs  1 ,  ' sa '
        
         if   exists  ( select   *   from   msdb.dbo.sysjobs a,#help_job b  where  a.job_id = b.job_id  and  a.name = ' job1 '   and  b.job_state  =   4   and  b.request_source_id  is   null )
         begin
             set   @job_run_flag   =   0
         end
         else
         begin
             -- 延时一分钟
             waitfor  delay  ' 000:01:00 '
         end

     end

     -- 执行其他作业
     exec  msdb.dbo.sp_start_job  @job_name   =   ' job1 '

     -- 更新服务器状态
     -- update SYS_Server set UpdateFlag = 0 where PID = @PID
     set   @job_run_flag   =   1

b.request_source_id is null一定要加上，否则多次启动同一个作业的时候有可能报错。

我的存储过程，大家可以借鉴一下

CREATE   PROCEDURE  proc_pmis_main
AS

-- 更新准备
exec  proc_pmis_updateready

-- 临时表
create   table  #help_job(
    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  NULL ,
    running  INT   NOT   NULL ,  --  BOOL
    current_step  INT   NOT   NULL ,
    current_retry_attempt  INT   NOT   NULL ,
    job_state  INT   NOT   NULL
)

-- 作业是否正在运行
  -- 1：正在运行;0:未运行
declare   @job_run_flag   int
set   @job_run_flag   =   1
declare   @PID   int
-- 循环提取数据
declare  area_cursor  cursor   for
select  PID  from  SYS_Server  where  UpdateFlag  =   1   and  UseFlag  =   1   order   by  PID

open  area_cursor
fetch   next   from  area_cursor  into   @PID
while   @@fetch_status   = 0
begin
     -- 判断作业是否正在运行
     while   @job_run_flag   =   1
     begin
         -- 删除临时表信息
         delete  #help_job
         -- 添加信息
         INSERT   INTO  #help_job
         EXECUTE  master.dbo.xp_sqlagent_enum_jobs  1 ,  ' sa '
        
         if   exists  ( select   *   from   msdb.dbo.sysjobs a,#help_job b  where  a.job_id = b.job_id  and  a.name = ' job_pmis_synch '   and  b.job_state  =   4   and  b.request_source_id  is   null )
         begin
             set   @job_run_flag   =   0
         end
         else
         begin
             -- 延时一分钟
             waitfor  delay  ' 000:01:00 '
         end

     end

     -- 执行同步作业
     exec  msdb.dbo.sp_start_job  @job_name   =   ' job_pmis_synch '

     -- 更新服务器状态
     -- update SYS_Server set UpdateFlag = 0 where PID = @PID
     set   @job_run_flag   =   1
     -- print (@PID)

     fetch   next   from  area_cursor  into   @PID
end
close  area_cursor
deallocate  area_cursor

drop   table  #help_job
GO

转载于:https://www.cnblogs.com/rock_chen/archive/2006/11/24/570795.html