Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Run jobs synchronously

If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously.
What if you want to wait for the job to finished?

Try this

CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job
(
       @jobName SYSNAME
)
AS
 
SET NOCOUNT ON
 
DECLARE       @jobID UNIQUEIDENTIFIER,
       @maxID INT,
       @status INT,
       @rc INT
 
IF @jobName IS NULL
      BEGIN
            RAISERROR('Parameter @jobName have no value.', 16, 1)
            RETURN -100
      END
 
SELECT @jobID = job_id
FROM   msdb..sysjobs
WHERE name = @jobName
 
IF @@ERROR <> 0
      BEGIN
            RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)
            RETURN -110
      END
 
IF @jobID IS NULL
      BEGIN
            RAISERROR('Job %s does not exist.', 16, 1, @jobName)
            RETURN -120
      END
 
SELECT @maxID = MAX(instance_id)
FROM   msdb..sysjobhistory
WHERE job_id = @jobID
       AND step_id = 0
 
IF @@ERROR <> 0
      BEGIN
            RAISERROR('Error when reading history for job %s.', 18, 1, @jobName)
            RETURN -130
     END
 
SET    @maxID = COALESCE(@maxID, -1)
 
EXEC   @rc = msdb..sp_start_job@job_name = @jobName
 
IF @@ERROR <> 0 OR @rc <> 0
      BEGIN
            RAISERROR('Job %s did not start.', 18, 1, @jobName)
            RETURN -140
      END
 
WHILE (SELECT MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0) = @maxID
      WAITFOR DELAY '00:00:01'
 
SELECT @maxID = MAX(instance_id)
FROM   msdb..sysjobhistory
WHERE job_id = @jobID
       AND step_id = 0
 
IF @@ERROR <> 0
      BEGIN
            RAISERROR('Error when reading history for job %s.', 18, 1, @jobName)
            RETURN -150
      END
 
SELECT @status = run_status
FROM   msdb..sysjobhistory
WHERE instance_id = @maxID
 
IF @@ERROR <> 0
      BEGIN
            RAISERROR('Error when reading status for job %s.', 18, 1, @jobName)
            RETURN -160
      END
 
IF @status <> 1
      BEGIN
            RAISERROR('Job %s returned with an error.', 16, 1, @jobName)
            RETURN -170
      END
 
RETURN 0

Print | posted on Thursday, November 27, 2008 2:48 PM | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Run jobs synchronously

re: Run jobs synchronously
3/25/2009 9:12 AM | re: Run jobs synchronously
Gravatar

# re: Run jobs synchronously

Thanks for the above, but the code will not work if the job is brand new (ie no instance in the jobhistory table).

Try something like this (instead of the two-line WHILE loop):

--===========================================================
declare @iMaxInstanceID int -- put this up the top



select @iMaxInstanceID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0
set @iMaxInstanceID = ISNULL(@iMaxInstanceID, -1)

while (@iMaxInstanceID = @maxID)
begin
WAITFOR DELAY '00:00:01'
select @iMaxInstanceID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0
set @iMaxInstanceID = ISNULL(@iMaxInstanceID, -1)
end

--===========================================================
9/23/2010 12:52 AM | Andrew Jens
Gravatar

# re: Run jobs synchronously

Hrm, I tried to copy and paste this into a SP and it didn't like it:
Msg 102, Level 15, State 1, Procedure usp_Start_And_Wait_For_Job, Line 56
Incorrect syntax near '='.
4/29/2011 5:12 PM | Jeremy
Gravatar

# re: Run jobs synchronously

Yes,
there is a space missed out.

just replace that exec line by

EXEC @rc = msdb..sp_start_job @job_name = @jobName

6/13/2011 3:30 PM | Pete Midgley
Gravatar

# re: Run jobs synchronously

Was looking a solution to a problem I was having, and lo and behold I found it on this page.

Many thanks chap

G
8/19/2011 4:19 PM | G
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET