November 19, 2012 at 4:39 am
SQL JOBS Not Functioning :w00t:
I have Stored procedure & that stored procedure needs to be executed on 20 Databases daily,
Currently i am doing it manually..
I tried setting the Jobs on SQL but it only executes the SP for One database & not for all DB's.... :w00t:
how do i set the JOBS one after one..
I mean...
1st the SP for one DB will be executed & only after that the SP should get executed of rthe second DB & so on....:crazy: somehow i am not getting the exact steps or i may be missing something.. please help me
************************************
Every Dog has a Tail !!!!! :-D
November 19, 2012 at 4:47 am
Something like this?
EXEC DB1.dbo.MySP
EXEC DB2.dbo.MySP
EXEC DB3.dbo.MySP
EXEC DB4.dbo.MySP
EXEC DB5.dbo.MySP
EXEC DB6.dbo.MySP
EXEC DB7.dbo.MySP
EXEC DB8.dbo.MySP
EXEC DB9.dbo.MySP
EXEC DB10.dbo.MySP
EXEC DB11.dbo.MySP
EXEC DB12.dbo.MySP
EXEC DB13.dbo.MySP
EXEC DB14.dbo.MySP
EXEC DB15.dbo.MySP
EXEC DB16.dbo.MySP
EXEC DB17.dbo.MySP
EXEC DB18.dbo.MySP
EXEC DB19.dbo.MySP
EXEC DB20.dbo.MySP
John
November 19, 2012 at 4:51 am
Yes John, same to same 🙂
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 2:50 am
john, where did u go:w00t:
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 2:56 am
I didn't go anywhere. What is your question?
John
November 20, 2012 at 2:58 am
how do i set the JOBS one after one..
I mean...
1st the SP for one DB will be executed & only after that the SP should get executed of rthe second DB & so on....
Is this possible to perform the above activity in One Single Job???
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 2:59 am
Does the same procedure exist in all 20 databases and does it need any special paramateres to run?
November 20, 2012 at 3:00 am
Not only can you do it one single job, you can do it in one single job step, in the way I suggested above.
John
November 20, 2012 at 3:16 am
Yes the procedure is same for all database, but John can u let me know the steps by step about setting the jobs?
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 3:21 am
(1) Modify the code I posted so that it fits your environment
(2) Create a new job
(3) Copy and paste the code into the "Command" field
(4) Schedule the job to run with the desired frequency and at the desired times
Clearly you're struggling with something, but remember I'm not standing behind you and I can't see your screen, so if there's anything you don't understand, please be very specific about what it is.
John
November 20, 2012 at 3:27 am
Cool John, Thanks Man 😉
I will do it today itself & will let you know tomorrow..
Just one question..
IF i set the SP pne after other then..
The sequence of execution will be one after other???
I mean 1st sp excutes & then after it finishes themn only second Sp will execute???
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 3:31 am
That's right, yes. You might want to test what happens if one command fails - does it abort the whole batch or does it move on to the next command. If it moves on and that isn't what you want, then you'll either need to include some error handling, or split your job into 20 separate steps.
John
November 20, 2012 at 3:40 am
20 seperate steps is okas of now...
But in another Server there are almost more than 100 Db's :w00t:
Anyways will test it step by step, incase i have any issues, will write here.. thanks John 🙂
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 4:02 am
Could create a maintenance plan for the steps and just add them in one at a time with a success constraint. Pretty much the same thing as doing it in a job, however there's the handy little success constraint that you can add to ensure the next step doesn't execute until the previous one is successful.
November 20, 2012 at 4:17 am
Hi Shell,
Acccording to John, i have followed the below step
(1) Modify the code I posted so that it fits your environment
(2) Create a new job
(3) Copy and paste the code into the "Command" field
(4) Schedule the job to run with the desired frequency and at the desired times
Now how will i confirm myself that the 2nd sp gets executed only after 1st one finishes and so on??
U have any idea
************************************
Every Dog has a Tail !!!!! :-D
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply