0

I am using SQL Server 2008 Express and I cannot buy the full version it is not in the budget.

What I need is after an insert is done to call a stored procedure to send a notification email after a certain amount of hours. This amount of hours is also stored in the database for configurable reasons.

I was thinking about using windows scheduler and running a stored procedure twice a day but if the user set it to run less than 12 hours that will lead to the user being notified two times or more. I also certainly don't think it is wise to run it every hour. So both these options don't seem like the best.

I was wondering is there was a way to time something like this or even schedule it to run at a certain time (using express remember)

Any Help would be greatly appreciated and thanks for reading!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user758105
  • 77
  • 1
  • 7
  • 3
    Why wouldn't it be wise to run every hour? I've written plenty of notification parts of apps that run every hour to check for the latest notifications to send. – Eric Jan 09 '12 at 22:17
  • hmm to me it just seemed like a bad idea to have to run a job every hour taking up resources when I could do it all at once. But I guess it really is 6 in one hand half a dozen in the other hand. – user758105 Jan 09 '12 at 22:30
  • Why would the user receive more than one notification if the scheduler is run more often? You should flag or delete records in your DB after sending the email, to avoid repeats. – RickNZ Jan 10 '12 at 03:50

3 Answers3

1

Use conversation timers. In the insert you would start a conversation timer armed to fire after the number of hours desired. When the time passes the system will enqueue a message and you can use interval activation to run the procedure you want, including sending a message. The advantage of this implementation is that it relies only on SQL Express features. It is also reliable, you won't loose notifications if a process shuts down, like it would happen with a CLR or WAITFOR based solution.

See Asynchronous procedure execution for a similar idea, but w/o a timer.

Even if you end up doing a check every hours (or every 5 mins) for pending 'due' notifications, I would still use a Service Broker activation based mechanism to activate the 'check' task. I also recommend reading Using tables as Queues.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

you can use a CLR project in visual studios to achieve this!

The trigger will listen for any insert done on table and in return call a webservice that sends email.

demo.b
  • 3,299
  • 2
  • 29
  • 29
  • The email isn't the hard part, I need it delayed for a certain amount of time. It is an old project with classic asp and I already have the database able to send emails. I just need a way to delay them for the set amount of time. Thank you for your answer. – user758105 Jan 09 '12 at 22:28
0

If you can use the SQL Server WAITFOR command it might be helpful.

Maybe a sql stored procedure that runs every hour, and checks to see if its configured to run on "this" hour, if so then send the email.

http://msdn.microsoft.com/en-us/library/ms187331.aspx

Kyro
  • 748
  • 1
  • 12
  • 28