SQL Server Performance

Send a reminder

Discussion in 'General DBA Questions' started by mariechristine, Nov 28, 2003.

  1. mariechristine New Member

    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?
  2. BHB New Member

    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()

Share This Page