Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Aaron Bertrand Dec 17 '12 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 '12 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. –  Aaron Bertrand Dec 17 '12 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. –  Mark Storey-Smith Dec 17 '12 at 23:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.