SQL JOBS Not Functioning

  • 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

  • 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

  • Yes John, same to same 🙂

    ************************************
    Every Dog has a Tail !!!!! :-D

  • john, where did u go:w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • I didn't go anywhere. What is your question?

    John

  • 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

  • Does the same procedure exist in all 20 databases and does it need any special paramateres to run?

  • 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

  • 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

  • (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

  • 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

  • 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

  • 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

  • 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.

  • 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