记录：获取 sql server 中job的运行状态

USE   msdb
declare  @temp table(
session_id int ,
job_id uniqueidentifier ,
job_name sysname ,
run_requested_date datetime ,
run_requested_source sysname ,
queued_date datetime ,
start_execution_date datetime ,
last_executed_step_id int ,
last_exectued_step_date datetime ,
stop_execution_date datetime ,
next_scheduled_run_date datetime ,
job_history_id int ,
message nvarchar(1024) ,
run_status int ,
operator_id_emailed int ,
operator_id_netsent int ,
operator_id_paged int)

 

insert into @temp exec sp_help_jobactivity @job_name = N'job name';

select * from @temp;

 

------------------------------------------------------------------------------------------------------

create procedure execute_job_with_block
@jobname varchar(1000)
as
begin
 print @jobname+' start';
    /*test the job status, if the status is ok, let it go*/
 waitfor delay '00:00:01';
 print @jobname+' end';
end

exec execute_job_with_block N'job1';
exec execute_job_with_block N'job2';
exec execute_job_with_block N'job3';