How to implement SQL Server Job dependencies

Various ways to implement SQL Server job dependencies:

  1. We need to start job B after job A finishes. In the last step of job A add a step to start job B using SQL code – EXEC msdb..sp_start_job @job_name = ‘Job B’.
  2. We need to start job B after job A finishes successfully, but if Job A fails then execute some SQL statement, send email notification and start job C. In above scenario we can have following SQL code –

Declare @Status char(4)
set @Status = ‘N’
declare @body1 nvarchar(MAX)
IF Exists (select distinct run_status from msdb.dbo.sysjobhistory
where job_id = (select job_id from msdb.dbo.sysjobs where name = ‘Job A’)
and run_status = 1
and run_date = cast(convert(char(8),getdate(),112) as int)

)set @Status = ‘Y’
else set @Status = ‘N’
WAITFOR DELAY ’00:00:05′
if @Status = ‘Y’
Begin

EXEC msdb..sp_start_job @job_name = ‘Job B’
End

Else
Begin
–set of SQL statements for recovery action
set @body1 = ‘Server :’+@@servername + ‘ Job A failed and hence job C Recovery started’
EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com; abc1@xyz.com’,
@profile_name = ‘EMAIL PROFILE NAME’,
@subject = ‘Job A failed and hence job C Recovery started’,
@body = @body1,
@body_format = ‘HTML’ ;

EXEC msdb..sp_start_job @job_name = ‘Job C’

End

3. We need to start job B, running every hour till 5 pm, after job A finishes successfully, sometime in the early morning. Create scheduler for job B to run every hour starting from 9 am to 5 pm.

Now the first step in job A should be to disable the job B scheduler:

Use msdb

go

EXEC dbo.sp_update_schedule     @name = ‘SchldPSIncrementalLoadRefreshEvery1Hour’,     @enabled = 0

Now the last step in job A should be to enable the job B scheduler:

Use msdb

go

EXEC dbo.sp_update_schedule     @name = ‘SchldPSIncrementalLoadRefreshEvery1Hour’,     @enabled = 1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s