Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Shcedule set of SP,executing sequencly Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 11:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:50 AM
Points: 135, Visits: 260
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.!
Post #1396878
Posted Saturday, December 15, 2012 9:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 35,572, Visits: 32,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1396899
Posted Saturday, December 15, 2012 9:03 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1396961
Posted Sunday, December 16, 2012 10:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:50 AM
Points: 135, Visits: 260
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!
Post #1397085
Posted Sunday, December 16, 2012 11:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1397100
Posted Monday, December 17, 2012 1:44 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1397413
Posted Monday, December 17, 2012 10:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:50 AM
Points: 135, Visits: 260
Thank You Very Much!
Post #1397547
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse