I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code and each sending out its own individual email. The jobs are all on a schedule, some execute every 2 hours, some every 15 minutes, some once a week.

What I want to accomplish:

  • Turn each job into a stored procedure.
  • Create a user control table so that users only receive relevant alerts via SQL mail.
  • Have one SQL job that controls everything from a job control table.

Basically, I want to move the maintenance and scheduling of these alerts out of SQL Server Agent and put it into a control table. Can this be accomplished so that each stored procedure runs on its own schedule without the need for having numerous, unwieldy jobs?

  • 1
    Why do you want one job to rule them all? You can still control alerting through a table without trying to implement this single Frankenstein job that does all of your bidding. I think your jobs can be a lot less chaotic once you move your alerting out of there. Dec 17, 2012 at 19:09
  • @AaronBertrand There are new alerts being added on a fairly regular basis and, since there is no real control structure, the entire company is being inundated with alerts every time they run. The ultimate goal is to make it so that each alert only goes out to certain people and the recepients can be easily adjusted in a table. I assumed that an uber-job would be the best route to take in order to accomplish this.
    – Davenport
    Dec 17, 2012 at 19:36
  • Why would it be easier to manage this from one job vs. many? It would probably also be easier to manage all of our data in a single uber-table, but we don't do that for a variety of reasons. Dec 17, 2012 at 19:40
  • 2
    Agree with @AaronBertrand. Try solve the problem by fixing the way the existing built-in tools are used first, rather than try to craft a SQL Server Agent 2.0. Adding to that built-in feature set with something like SQL Sentry Event Manager might be worth consideration, if necessary. Dec 17, 2012 at 23:42


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.