Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-10 : 08:16:44
|
| Pixelz writes "Hi all,I am just starting out along the wonderful road of SQL programming. As it stands I've only really queried a database to rerieve, add and delete information - remotely made my first table the other day which was a thrill I tell ya.However, here's my issue - a new potential client of mine has asked if it would be possible to program in an alert system to notify his users, by email, when their subscriptions are due to expire! hmmmmm... this seems like quite a tricky one and I'm not quite sure where to start.I will be coding this site in trad ASP (with JScript) as I don't know anyting else well enough right now. I expect that I'll want to use a datestamp to record WHEN they joined and maybe a comparison to todays date to work out when the email should be sent, but how do I get the database to do this by itself? It would probably have to check every day, but I don't know how to get it to do that.Any help would be greatly appreciated!!ThanksPixelz" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 08:36:11
|
| have a look at the agent. You can add a job which executes a stored procedure (or other things) on a schedule - you would want this once a day I guess.The stored proc can get all the subscriptions that are about to expire and use xp_sendmail to send an email.You would probably want to record emails that are sent.I would populate a table from that sp with the emails that are to be sent and have another process that actually sends the emails and marks the entry as complete. In that way it's easy to change the email sending solution (sql servers not that good at it but it's easy to do for an initial version).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 08:46:44
|
| We send "reminder emails" from the application, not from SQL Server's agent itself.FWIW: We store a date when the email was successfully sent so that we can run the emailing tool as often as we like, and it won't re-email anyone who was already emailed.So, it works something like this:If Renewal is due AND EMail not sent:1) Send Email2) Update "Sent Date"Then we have a routine to clear the Sent Date when a Renewal is received.You may also want a Reminder Number field - so:If Reminder Number = 1 and Sent Date more than 1 week ago:1) Sent Second Reminder2) Update Reminder Number to 2 and set Sent Date to TodayKristen |
|
|
|
raclede
Posting Yak Master
180 Posts |
Posted - 2006-05-11 : 01:14:26
|
| I agree with Kristen, its much easier if we do it using an application.. our email notification is a C# Project that is setup with Windows Task Scheduler that regularly connects to a database. |
|
|
|
|
|
|
|
|
email alerts and databases