SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Shcedule set of SP,executing sequencly


How to Shcedule set of SP,executing sequencly

Author
Message
avdhut.k
avdhut.k
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 266
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.!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84553 Visits: 41064
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
j.miner
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 358
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
avdhut.k
avdhut.k
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 266
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!
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5170 Visits: 4076
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;-)
j.miner
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 358
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
avdhut.k
avdhut.k
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 266
Thank You Very Much!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search