How to send reminder notifications to subscribers for renewal from SQL Server 2005?

Question

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

Answer 1

You can create a .NET CLR database project and load the DLL assembly into SQL Server. You can then create a job on SQL server to call your assembly, which will run your code (possibly sending emails to the expiring users).

Check this for creating the assembly: http://www.mssqltips.com/tip.asp?tip=1795

Check this for creating the job: http://msdn.microsoft.com/en-us/library/ms190268.aspx