CODEWITHSUNDEEP
sql-serversql-server-2008-express
user758105
user758105

Reputation: 77

Call a stored procedure after a configurable amount of hours after an insert

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!

Upvotes: 0

Views: 675

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294317

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.

Upvotes: 1

Kyro
Kyro

Reputation: 768

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

Upvotes: 0

demo.b
demo.b

Reputation: 3439

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.

Upvotes: 0

Related Questions