locked
SQL Server Maintenance Plan "Flow" RRS feed

  • Question

  • SQL Server 2008 R2

    We have a number of fairly simple plans in place which run T-SQL scripts and send emails (notify operator) - primarily these are for out-of-hours data exports. One that concerns me had 10 items in it, and the quickest route from start to end is through 5 tasks all connected with "success" lines. There are a couple of "fail" lines that are there to hand a retry of our exports, but by all account are not affected by my question.

    Can someone explain or help me understand why the flow stops as soon as it has completed a T-SQL task, which did not fail?

    The simple route as mentioned above is NOTIFY -> SQL -> NOTIFY ... and the second NOTIFY doesn't fire. There is a "failure" line from the SQL, which works when we inserted something invalid into the T-SQL code.

    Any help appreciated


    Regards Sean Anderson

    Tuesday, July 10, 2012 9:00 AM

Answers

  • It sounds like you have multiple paths into a task and only one of the paths can possibly complete.  If that is the case then you need to right click on one of the lines go to properties and change it to an OR precedence

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by Fullingdale Tuesday, July 10, 2012 12:58 PM
    Tuesday, July 10, 2012 12:42 PM

All replies

  • You are going to have to post a picture of the task configuration and the step outputs from the job history for us to be able to help with this.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, July 10, 2012 12:31 PM
  • It sounds like you have multiple paths into a task and only one of the paths can possibly complete.  If that is the case then you need to right click on one of the lines go to properties and change it to an OR precedence

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by Fullingdale Tuesday, July 10, 2012 12:58 PM
    Tuesday, July 10, 2012 12:42 PM
  • I stripped this back to a single flow of tasks (using only SUCCESS lines)

    1. Notify Task "STARTED PROCESSING"
    2. Execute Procedure
    3. Notify Task "STEP COMPLETED"
    4. Execute Procedure
    5. Execute Procedure

    Worked fine.

    Then I added a branch off of step (2) with a FAILURE line leading to a newly added Notify Task for "PROCESSING FAILED" ... still works.

    Under that new task, then added another "Execute Procedure" joined with a SUCCESS ... still works.

    So, now I have the original 5 steps and a branch coming off of (2) with two steps that should trigger if (2) fails.

    What I noticed is that when I join the branch back into the original path (ideally at (3)) the symptoms of my original message occur.

    I have attached a sample image of the flow. Note that step (2) in this test is successful (only contains a simple 'SELECT * FROM SMALL_TABLE' statement for testing purposes)

    I trust that this makes sense.

    Note that all "EXECUTE" tasks only contain a "SELECT * FROM TABLE" and all "NOTIFY" tasks only have a simple email to my address


    Regards Sean Anderson

    Tuesday, July 10, 2012 12:57 PM
  • It sounds like you have multiple paths into a task and only one of the paths can possibly complete.  If that is the case then you need to right click on one of the lines go to properties and change it to an OR precedence

    Chuck Pedretti | Magenic – North Region | magenic.com

    Bingo!

    Thanks a million for that.


    Regards Sean Anderson

    Tuesday, July 10, 2012 12:58 PM