Post reply

How to Shcedule set of SP,executing sequencly

  • December 14, 2012 at 11:01 pm

    #272046

    Hi,

    I want Scedule the Set of Stored Procedures

    And if any one SP gets errors then next one should gets exceute.

    For Example,

    Execute Mis_SP_Earn_PJ_Mast_Extraction 'Earn_PJMast_201211','30-Nov-2012'

    EXECUTE Earn_Tran_Extraction '[UNO-III\HISTORY].MIS.DBO.Earn_PJMast_201211', 'Earn_PJTran_201211','12-Dec-2012'

    EXECUTE Earn_Actual_IRR 'Earn_PJMast_201211','Earn_PJTran_201211'

    The Above are the three SP's,

    I want to Sheduled it to execute it sequencely,

    and if in any one errors comes then next one should gets executed.

    Thanks in Advance.!

  • December 15, 2012 at 9:44 am

    #1568888

    Lookup "IF", "@@ERROR", and "RETURN" in Books Online.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 15, 2012 at 9:03 pm

    #1568923

    Create a job named J. Schedule each call as a seperate Step.

    As a default, the error in the Stored Procedure will be returned to the job.

    Use the conditions on the job step (success or failrue) to determine the execution path.

    The same thing can be done with SSIS.

    I guess what is the goal of this process. To be scheduled or to be called on demand?

    John Miner
    Crafty DBA
    www.craftydba.com

  • December 16, 2012 at 10:08 pm

    #1569010

    Hi John,

    Thanks for the reply,

    But I want to excute this SP Step by Step,

    And If in any one of the SP error comes then next one should be executed.

    Execute Mis_SP_Earn_PJ_Mast_Extraction 'Earn_PJMast_201211','30-Nov-2012'

    EXECUTE Earn_Tran_Extraction '[UNO-III\HISTORY].MIS.DBO.Earn_PJMast_201211', 'Earn_PJTran_201211','12-Dec-2012'

    EXECUTE Earn_Actual_IRR 'Earn_PJMast_201211','Earn_PJTran_201211'

    Can You please provide the steps or link to do the same

    Thanks in Advance!

  • December 16, 2012 at 11:43 pm

    #1569018

    avdhut.k (12/16/2012)

    Hi John,

    Thanks for the reply,

    But I want to excute this SP Step by Step,

    And If in any one of the SP error comes then next one should be executed.

    Execute Mis_SP_Earn_PJ_Mast_Extraction 'Earn_PJMast_201211','30-Nov-2012'

    EXECUTE Earn_Tran_Extraction '[UNO-III\HISTORY].MIS.DBO.Earn_PJMast_201211', 'Earn_PJTran_201211','12-Dec-2012'

    EXECUTE Earn_Actual_IRR 'Earn_PJMast_201211','Earn_PJTran_201211'

    Can You please provide the steps or link to do the same

    Thanks in Advance!

    this can be done with the help of JOB see http://msdn.microsoft.com/en-us/library/ms191439.aspx

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 17, 2012 at 1:44 pm

    #1569274

    Again, a job is one way to do this.

    See "Set Job Step Success or Failure Flow" - http://msdn.microsoft.com/en-us/library/ms177461.aspx - Conditions for optional execution.

    I tried to elaborate more on the tasks.

    Psuedo Code:

    Create Job A

    Create Step 1, Call Stored Procedure 1

    Create Step 2, Call Stored Procedure 2

    Create Step 3, Call Stored Procedure 3

    Here is where all the magic happens! Look at Job Step Properties dialog box, select the Advanced page.

    Step 1 - On success, exit with success code.

    Step 1 - On Failure, goto step 2

    Step 2 - On success, exit with success code.

    Step 2 - On Failure, goto step 3

    Step 3 - On success, exit with success code.

    Step 3 - On Failure, exit with failure.

    This should execute one, two or three steps depending upon the return value of each stored procedure call.

    Sincerely

    John Miner
    Crafty DBA
    www.craftydba.com

  • December 17, 2012 at 10:40 pm

    #1569370

    Thank You Very Much!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply