How to send reminder notifications to subscribers for renewal from SQL Server 2005?
- by codemonkie
I have a table in SQL DB, namely dbo.subscribers, it contains following columns:
-SubscriberID
-JoinDateTime
The business logic says a subscription last for 2 weeks and a reminder should be sent after 7 days from the JoinDateTime.
The way that the system was designed to send reminders are via a URL call, e.g. http://xxx.xxx.xxx.xxx/renew_userid=SubscriberID/ and that can only be called from our webserver which is the only whitelisted IP machines given.
Currently there is a windows service written to query the DB once a day at midnight to grab all expiring subscribers and send them reminders, however this batch approach only sends reminders to the nearest date, well, I could have set the interval down from 1 day to 1 hour such that the service can send notifications out closer to the exact JoinDateTime + 7 days requirements.
I have heard a stored procedure can be written and perform task like this to a nearly real-time manner, if yes, please give me some hints on how to do it.
Another question is - is SSRS bit of an overkill to perform things like this?
Please advice.
TIA
