Automatic reminder emails two business days before event

Question

Automatic reminder emails two business days before event

RHJ

Mr or Mrs. 500

Points: 545
More actions
December 31, 2014 at 5:46 am

#151394

I work for a company that is a third party administrator for a state (read - old everything).
The company uses SQL Server 2005 for development inside the firewall and SQL Server 2000 (yes, I said 2000) outside the firewall for the public facing website.
I just wrote an ASP.NET webforms application to gather first name, last name, date of birth, and location and session time selection from individuals wishing to register for an event. I collect all this through a webform and store it in a database.
Now I have been tasked with creating a program to automatically generate a reminder email and send it out two business days before the event the individual is registered for. I have to have this "program" ready to go live by the end of the first week of January.
I am looking for suggestions on how to accomplish this (resources, example links, etc.) using SQL Server 2000. Please help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Step 1: create a calendar table that also include a column to flag a business day (e.g. tinyint with constraint 0,1). There are numerous blogs on how to build a calendar table including business days and holidays.

Step 2: create a job that runs every day, checks if it's a business day (to prevent sending emails out on a non-business day) and if there's a event two business days ahead continue otherwise exit the job.

Step 3: send the mail to the people registered for the event (search for "sql server 2000 sql mail" to find a few ways to do it)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

do you have dbmail working already on your SQL2000 instance?

sending mail of any kind is going to have that as a prerequisite.

after that, i'd say it's a query in a scheduled job that compares getdate() -2 to the eventdate in your table (you do have that in a table, right?)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 3

As Lowell suggested, I'd use SQLMail.

We used to send the newsletter here from SQL2000. It can be done at lower volumes (1,000s/day).

What I'd do is load an email table with reminders to be sent. When someone registers, find the 2nd day before using a calendar table (as Lutz mentioned), and insert the note with that date/time and an unsent flag.

Have a process running every hour (or whatever) that sends all emails that are unsent and are past due. We wrote a .Net program for this, but you could just do this with SQLMail. When an email sends, note it in the table with an update.

If there are any reg changes, update the reg and the reminder table at that time.

Couple things: If you don't use a mail library (.Net DLL), then have someone monitor your mailbox for hard bounces and unsubscribes. Also, make sure you have some archive or removal process for the mails.

RHJ Mr or Mrs. 500 Points: 545 More actions · Answer 4

Thank you for all your replies.

My experience is nill in doing this but it is what my manager requires. I'll continue doing research including the things you have all mentioned. We have a new SQL Server DBA too, but his primary experience has been with Oracle, so we are both on a learning curve. Just launched the registration app this morning, now I've got a few days to work on the email sendouts.

deniyo23nil SSC Rookie Points: 33 More actions · Answer 5

Hi Grasshopper! I am a software engineer and I just got same project for a corporate event planning company. I would follow all these instructions and try to build a working program for an Automatic reminder that emails two business days before event.