Writing smart DotNetNuke Schedulers that sends email

This post is for those who are using dotnetnuke scheduler for rapidly sending emails to the users. There are times when scheduler fails to complete the job and try running the job again base on retry setting applied to it. Bad news is that, the user who are expecting the email once receives the email twice. If you set retry frequency to an hour or so, and your code meets some bad data like null reference of so, users will get the email every hour.

This description at least clears the thing that we should at least not set retry frequency while sending emails through scheduler. Next thing comes to mind is, there should be a batter way to do it. I personally work on it and think about some way that adds some overhead to the scheduler activity but makes sure that no user will get the same email more than once.

Create a table called tracker that stores following things:
Id - Auto increment
Email - Email sent
Type - default 0, increment it in case if you are using more than one scheduler for sending emails
Date - Small date time that stores date and time the email sent to this user.

The next step is to modify your query to return email that are not in this table. For Example:

Select userid, email
from users u
where email not in (select email from tracker where u.email = tracker.email and convert(varchar(10),date,112) = convert(varchar(10),getdate(),112))

(Please be careful while comparing dates and times in t-sql. If you are using small date time to compare two dates, equality is base on date and time both.)

I think using this trick will never send email to the same user again.
Have fun with your development.