I have the following scenario that I'm trying to solve in SQL Agent.
Job 2 needs to kick off @ 1AM, but has a dependency on Job 1.
I've been trying to figure out if there is a way to schedule a specific step? That way instead of having a Job 2 I could have step 2 dependent on step 1 and have it scheduled for a later time.
Alternatively, I know that I could build a table to determine dependency. Similar to SQL Server Agent job dependency. However, I'm looking to make it intelligent enough that if Job 1 isn't finished yet by the scheduled time Job 2 will try to run again at a later interval.
There are different approaches.
The one is
1) Create the last step in Job 1 to start Job 2
exec msdb.dbo.sp_start_job @job_name = 'enter you job name here'
2) The first step of Job 2 should be TSQL command
WIATFOR DELAY xx:yy:zz
xx,yy,zz values should be calculated as time difference between now and required 1AM.
If NOW() is greater then 1AM, exit (complete) the step 1 to execute step 2 immediately.
Note that Job 2 in this case should not be scheduled.
Another method:
In Job 2, the first step in WHILE loop checks the status of job 1 (the code can be found easily online) with some delay, e.g. 1 minute. The loop exit condition is when Job 1 is complete.