SQL Jobs

Transact-SQL
1

Hi,
I have a script which sends the email to people when all jobs are successfully completed.
[
if exists
(select '1'
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
(select jt.id
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'
else
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.

  1. The script will need to be run several times in short intervals during a specified window (possibly 7 am to 10 am).
  2. 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.
  3. 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?

2

Setup a tracking and control table to keep track of what you've done or not.

3

Would this work?

Insert into Email table details of the emails to be sent:

INTO INTO MyEmailTable(EmailDate, EMailKey, Action)
SELECT CONVERT(Date, GetDate()) ,
       'xxx', -- The Code / ID for the Email being sent
       1 -- Indicate that the Email has not yet been sent
FROM SomeTable
WHERE NOT EXISTS
(
.... your original query checking if any  jobs are running
)
    AND NOT EXISTS
(
    SELECT *
    FROM MyEmailTable
    WHERE EmailDate = CONVERT(Date, GetDate())  --- Already sent today?
          AND EMailKey = 'xxx' -- The Code / ID for the Email being sent
)

i.e. this will insert a row(s) into the MyEmailTable for emails which need to be send, but not it the jobs are running and not if an email, for that EmailKey, has already been sent today.

Then send an email for all rows where [Action]=1 in the MyEmailTable and change the [Action] to, say, 2 (to indicate that the email has been successfully sent)

(That's basically what Jeff said, I just put some bones on it!)

(You could use something like Service Broker instead of storing the details in your own "queue table" MyEmailTable)

1 Like
4

Thanks Kristen ..Good solution..I will check and let you know..

5

Thanks..