Sp_start_job is waiting for the job

Question

Possible duplicate:
Executing a SQL Server Agent Job from a Stored Procedure and Returning the Result

Is there a way to determine when the sql agent job is completed after it is started using sp_start_job?

Answer 1

XP_SQLAGENT_ENUM_JOBS may be used, but it is not documented. It is usually used to detect long work tasks.

Of course, there are also sp_help_jobs or just keep track of work history tables

Answer 2

I really had to do this recently, and that is how I am going to implement it. I create a temporary job through sp_add_job and sp_add_jobstep and set @delete_level to 3 (always delete after launch).

I am not 100% selling this approach, as you can probably tell from the name of the stored procedure. However, it works:

 CREATE PROCEDURE spWorkaround_checkJobExists @job_id UNIQUEIDENTIFIER , @thisIteration tinyint , @maxRecurse tinyint AS IF (@thisIteration <= @maxRecurse) BEGIN IF EXISTS( select * FROM msdb.dbo.sysjobs where job_id = @job_id ) BEGIN WAITFOR DELAY '00:00:01' DECLARE @nextIteration int SET @nextIteration = @thisIteration + 1 EXEC dbo.spWorkaround_checkJobExists @job_id, @nextIteration, @maxRecurse END END 

Of course, you will want to paste the code to ensure the maximum number of times it takes, but you get this idea. You can also pass a parameter to control how often recursion occurs. In my case, after ten seconds, the results are meaningless.

What I do here can be changed for jobs that should not be immediately deleted after completion by changing the selection criteria to check the status of the job, for example, sp_help_job, passing @job_name or @job_id and @execution_status = 0.

Answer 3

  sp_help_job @job_name @execution_status = 0 

Answer 4

This article describes the SP for starting the sql agent job and waiting.

 -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table declare @xp_results TABLE ( 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) -- start the job declare @r as int exec @r = msdb..sp_start_job @job -- quit if unable to start if @r<>0 RAISERROR (N'Could not start job: %s.', 16, 2, @job) -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?) WAITFOR DELAY '0:0:01'; set @seccount = 1 -- check job run state insert into @xp_results execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id set @running= (SELECT top 1 running from @xp_results) while @running<>0 begin WAITFOR DELAY '0:0:01'; set @seccount = @seccount + 1 delete from @xp_results insert into @xp_results execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id set @running= (SELECT top 1 running from @xp_results) end -- result: not ok (=1) if still running if @running <> 0 begin -- still running return 0 end else begin -- did it finish ok ? set @run_status = 0 select @run_status=run_status from msdb.dbo.sysjobhistory where job_id=@job _id and cast(run_date as bigint) * 1000000 + run_time >= @start_job if @run_status=1 return 1 --finished ok else --error RAISERROR (N'job %s did not finish successfully.', 16, 2, @job) end END TRY 

Answer 5

 SELECT TOP 1 1 AS FinishedRunning FROM msdb..sysjobactivity aj JOIN msdb..sysjobs sj on sj.job_id = aj.job_id WHERE aj.stop_execution_date IS NOT NULL AND aj.start_execution_date IS NOT NULL AND sj.name = 'YourJobNameHere' AND NOT EXISTS ( SELECT TOP 1 1 FROM msdb..sysjobactivity New WHERE New.job_id = aj.job_id AND new.start_execution_date > aj.start_execution_date )