SQL Server dependency Job to check if Job X and Job Y are completed then start Job A

Job A has been scheduled to run at 7.30 and keep checking till 8.30
Code:
—————————
Declare @endtime datetime, @Status char(1)

set @Status = ‘N’
set @endtime = (select dateadd(dd,0, datediff(dd,0,getdate())) + dateadd(ss,(08*3600)+(30*60)+0,0)as Datetime)

while getdate() < @endtime and @Status = 'N'
–while @Status = 'N'
begin

IF
(
(Exists
(select distinct run_status from msdb.dbo.sysjobhistory
where job_id = (select job_id from msdb.dbo.sysjobs where name = 'Job X')
and run_status = 1
and run_date = cast(convert(char(8),getdate(),112) as int)
)
)
and
(Exists
(select distinct run_status from msdb.dbo.sysjobhistory
where job_id = (select job_id from msdb.dbo.sysjobs where name = 'Job Y')
and run_status = 1
and run_date = cast(convert(char(8),getdate(),112) as int)
)
)
)
set @Status = 'Y'
end

if @Status = 'Y'
EXEC msdb..sp_start_job @job_name = 'Job A'
Else
Begin
declare @body1 nvarchar(MAX)
set @body1 = 'Server :'+@@servername + ' Job A has not started'
EXEC msdb.dbo.sp_send_dbmail @recipients='abc@xyz.com',
@profile_name = 'EMAIL PROFILE',
@subject = 'Job A has not started',
@body = @body1,
@body_format = 'HTML' ;

End

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