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 several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that sql server agent is not able to include jobs as job steps to be executed. I did try

share|improve this question
1  
+2 I was thinking about this last night :) –  jcolebrand Jul 1 '11 at 16:35
1  
does each of your items need to be a separate job? Could they be setup into a master job with separate job steps and for each job step add t-sql or other logic which determines whether the step should run and how (e.g. if (condition = true) do work else do nothing. –  John DaCosta Jul 3 '11 at 23:01
    
Thanks everybody for giving me some help, it seems that Aarons suggested way is getting the closest to what I need. I will see if I get permission for a tool to do it, otherwise within SQL Agent I will have to make a monster job :(. –  nojetlag Jul 4 '11 at 16:36
    
You say "packages", do you mean SSIS packages? –  SqlSandwiches Jul 4 '11 at 22:19
    
agreed with John DaCosta. Use steps with conditions. –  Alex_L Aug 17 '11 at 1:02
add comment

3 Answers

up vote 3 down vote accepted

There are 3rd party tools out there that will allow you to do this, if you don't have the learning curve time or the existing expertise to use SSIS. Disclaimer: I work for one of those companies. Check out Event Manager from SQL Sentry - it handles SQL Server jobs (including SSIS packages), Windows Scheduled Tasks, and even Oracle jobs. Here are the SQL Server features (including chaining and queuing):

http://sqlsentry.net/event-manager/sql-server-enterprise-features.asp

Unfortunately I don't think you'll be able to use SQL Agent on its own to chain multiple jobs. The sp_start_job method that Marian suggested kicks off jobs asynchronously; you can't wait for them to complete before moving on to the next command or the next step.

share|improve this answer
    
Umm, I think he can query the msdb system tables though, and have a status of the other jobs (and their steps) status. If I'm wrong, I'll apologize and give you a 6 pack for revealing this :-). –  Marian Jul 2 '11 at 17:57
    
Don't understand your point. How is querying the msdb tables going to help him chain jobs together? WHILE 1=1 until the status changes? Query notifications? Sure there are many ways you can do this manually, but it's going to be a heck of a lot more complex than just calling sp_start_job. –  Aaron Bertrand Jul 4 '11 at 3:28
add comment

SQL Agent is not considered to be "enterprise class" as a job scheduler. It lacks a lot of functionality that you end up having to build yourself. One such example would be dependencies. As a result you are forced to put most of this logic into SSIS packages. Not necessarily a bad thing...just a pain to build and manage yourself.

So, that's my answer, build out an SSIS package and use some t-sql commands to call SQL Agent jobs as desired. Make sure you have your requirements and scenarios clearly defined. You may not want to have multiple jobs hitting the same tables at the same time, for example.

HTH

share|improve this answer
    
Silly me expecting a "enterprise" scheduler in such a mature "Enterprise Edition" product :). I had hope since i can include plenty of things as a step, funny that the most obvious (another job) is not available as executable object. I use the jobs to run SSIS packages which have plenty of complexity, having to build another SSIS package to flexibly run the jobs seems not the right approach to me. Especially since I will end up again with the asynch sp_start_job. –  nojetlag Jul 4 '11 at 16:13
add comment

You should try by using a T-SQL script in your jobs' steps, using system stored procedure sp_start_job. It will allow you to chain how many jobs you want.

share|improve this answer
    
As mentionend by Aaron, it won't allow me to keep a given sequence, since they will all fire nearly at the same time (due to the fact that they are asynch). So this is not really a chaining approach, rather a collection approach :) –  nojetlag Jul 4 '11 at 12:51
    
Ok, maybe it's not very straightforward, but you can still query the msdb system tables, you can wait for a specific time.. then query again. Or you can make a tracking table where you put the jobs steps status and based on a trigger on it you could automatically fire the next steps. Or you can buy a tool that does that and adds some more value and also shows fancy UI :-). There are options for every one.. –  Marian Jul 4 '11 at 17:23
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.