I have a script which sends the email to people when all jobs are successfully completed.
from [System]..[Job] j WITH(NOLOCK)
left outer join [System]..JobMachine jm WITH(NOLOCK)
ON jm.JobID = j.id
where stateid = '4' -- running
and jm.Machine in ('ABC', 'PQR')
and j.TemplateID not in
from [System]..[JobTemplate] jt
where [Description] = '#executescript?name=SendDailyProcessingCompletedEmail'
and jt.TriggerTypeID = (select tt.ID from [System]..[TriggerType] tt where tt.[Description] = 'After')
set @JobsRunning = 'Y'
set @JobsRunning = 'N'
if @JobsRunning = 'N'
--send email logic](http://SQL JObs)
Sometime I am getting @JobsRunning = 'Y' then I am unable to send email.
Below are the my requirements.
- The script will need to be run several times in short intervals during a specified window (possibly 7 am to 10 am).
- If the email is sent or has already been sent for the calendar day, do not send the email on any subsequent iteration(s) for that calendar day.
- If the email is not sent and not been sent on the calendar day, allow for the possibility of the email being sent in the next iteration for that calendar day.
Can somebody tell me What would be the best approach?