November 11, 2008 at 8:39 am
(This may not be the appropriate forum to ask this question. If it isn't, then I apologize up front for making the mistake of posting in the wrong forum.)
We've got a SQL Server 2005 Standard Edition we use. I work at a nonprofit agency, and tracking our grant fund burn rate is an important issue. I know that SQL Server is capable to sending out emails to users, based upon some criteria, but I don't know how to do that. I've never set it up in any previous version of this version of SQL Server. What I would like to do is set up a job which will watch a value in one of our tables, which represents how much money we have left. Once it goes below a certain point, then I would like an email generated and have it sent to myself and others, saying that the fund has gone below such-and-such a value. However, once it has gone below, let's say $300K, then I don't want it continually sending out emails about that - one email is enough. I just don't know how to go about setting up such a thing. Do I write some sort of job which runs on a scheduled basis? And how do I go about setting up the email, etc? Does Notification Services have to have an email account of its own? (That wouldn't do, BTW.)
Or is this question asked in the wrong forum, and if so where should I ask it?
Kindest Regards, Rod Connect with me on LinkedIn.
November 11, 2008 at 8:49 am
I'll move this to the strategies forum.
The way I'd do this is in stages. First I'd get mail working. Look up Database Mail, get that setup and tested.
Next, I'd create a table that stores a date of entry, some amount, some recipient, some status, and a send date. I'd have a job that reads that table for unsent messages (say null send date), and if there are any, send an email to the recipient with the amount in it and marks the row as sent.
Now I'd create a job that looks for the amounts. If it finds some trigger amount (you could put this in another table, and I would), and checks for things being lower. If they are, then it checks the table above (prev para) to see if that amount has an email that is sent. If not, add a row, if so, ignore.
You would have to think about your conditions. How do you know when a 2nd email gets sent. I'd also think about a report being generated to you every day with this and other critical stats info. you'll get used to it, but what you can do is if something changes or hits some level, highlight it in red or make it jump to the top.
This help?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply