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 - 2001-11-30 : 09:29:30
|
| Michael writes "Hi there,I have a simple database that stores calendar items.I have CalendarID, UserID, StartDate, EndDate, ReminderHours and Description as the columns. (Where a User can have many Calendar Items)When a user enters a record, they can specify 5 for the ReminderHours and I want the database to automatically send an email to the user (using UserID) if there are 5 hours remaining until the StartDate occurs.Is it possible for SQL server to check for this and generate an email on this event? If so, how can it be done?The database is used through an ASP designed frontend." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-30 : 09:58:15
|
| Probably the easiest way to do this is to have a scheduled job that runs once an hour. It will query the table for any appointments that scheduled within the next five hours, and then email the user. The query won't return a row if the scheduled time has passed. This should do it:SELECT * FROM Calendar INNER JOIN Email ON (Calendar.UserID=Email.UserID)WHERE DateDiff(h, getdate(), Calendar.StartDate) Between 0 AND 5The SP will process these results and send them through SQL Mail. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-11-30 : 15:36:17
|
| Michael, beware of volume. If you expect this to be handling a large number of users/Appointments, you will probably find that SQL Server is not an efficient Bulk Mailer system. I don't have any numbers to tell you at what point it will become a burden, but just thought I'd throw out that heads-up for you.-------------------It's a SQL thing... |
|
|
|
cbrinson
Starting Member
36 Posts |
Posted - 2001-11-30 : 18:05:26
|
quote:
you will probably find that SQL Server is not an efficient Bulk Mailer system.
Sorry I am not much help here Michael but I have similiar questions. Does anyone know what would be the best way to implement this kind of system if SQL Server is not a good choice? I run a site that generates about 5-6 Million impressions a month. I wrote the bulletin board from scratch and I like it since it integrates well with the site. But it lacks many features of commercial bulletin boards such as email notification when someone replies to a thread you posted in. I was thinking that I would have a trigger that would fire when someone replies to a post. It would search a table for users that have chosen to monitor that post and email them. However, this will mean that SQL Server will be sending HUGE volumes of email daily. Any ideas?Thanks,Chris |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-11-30 : 18:27:12
|
| Chris,You might read this article by Merkin: [url]http://www.sqlteam.com/item.asp?ItemID=5908[/url] on building a Mailing Queue. I don't know which product would be the best email generator, but this will give you an idea on how you could set up your SQL Server to use a third-party product.Also, you might want to ask Graz how SQLTeam handles this. Or get the Snitz Forum code to see what they do. It might be the case that you would not be generating too large a volume.-------------------It's a SQL thing...Edited by - AjarnMark on 11/30/2001 18:28:53 |
|
|
|
|
|
|
|
|
SQL Mail