Send a reminder | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Send a reminder

I have a table called Booking which has an attribute reminder date.
I want that whenever the current date = reminder date, I want a notification to be sent to users i specify. The notification is just a message. And I want to set the frequency of the notification display. How to do that?
You need 4 fields…
SendEmailTime
EmailSentTime
Frequency (hour, day, month…)
Increment (how many hours, days…)
Create a job in sql server that opens a cursor and selects each row that needs an email sent. This would be the where clause.
WHERE getdate <= SendEmailTime AND EmailSentTime <= SendEmailTime. For each of these records…
1.) Send the email using xp_SendMail
2.) Calculate the next SendEmailTime…
IF @Frequency = ‘hour’ BEGIN
SET @SendEmailTime = dateadd("hour", @Increment, getdate())
END ELSE IF @Frequency = "day" BEGIN
SET @SendEmailTime = dateadd("day", @Increment, getdate())
END ELSE IF… 3.) Update SendEmailTime = @SendEmailTime, EmailSentTime = getdate()
]]>