4

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run. for eg: i have a a 2 step job and the second step must run and the first step has to be disabled or stopped along with the other jobs.

First of all i thought of stopping the sql server agent, but that will stop all the jobs. Is there any way to do it.

7

You can't disable a particular job step.

You have two options, the way I see it:

  1. Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps)

  2. Create a separate job with the step's logic and then only use the new job that does the work

|improve this answer|||||
  • I agree with Thomas. He illustrates two very simple means to achieve the result of skipping the steps as requested. – SQLRNNR Sep 24 '14 at 13:46
  • so, i created a separate job with the steps and will be manually running it. How can i stop the rest of the job other than going manually to each one of them and stopping it. Because the jobs are run on different times, i cannot manually stop it unless it is running. I get an error message when i try to stop the jobs which are not running. error : Stop failed for Job "job name" Error :22022. Request to stop job refused because the job is not currently running. How can i over come that and stop the rest of the jobs and run just 2 ? – user48062 Sep 25 '14 at 13:31
1

Another option, if you expect to be "playing" around with the jobs order very often, you can follow the following steps. We use it extensively and it works flawlessly.

  1. create a dummy step that just
    • let it run tsql code like: WAITFOR DELAY '00:00:01'
    • select Quit the job reporting success behavior for On success action on Advanced tab of step configuration
  2. place that step where you need to stop job execution
  3. save the job, it will detect that some of the steps will not be executed and warn you about, select yes and you're done.

Here is a picture to illustrate it. Dummy step is on position 8 so step on 9 will not be executed.

Show steps on job

|improve this answer|||||
  • Question is about skipping first step. Your answer is about skipping last step. – Ilya Berdichevsky Sep 24 '14 at 15:22
  • Question was about not running one particular step of a job and as example the OP mentioned not running 1st one. Is as easy as taking that one and changing the order to, say 9, on my example. Then which used to be 2nd, now is the first...easy peasy. With my answer you can leave out of the job as many steps you want without the need of creating more jobs, deleting steps or whatever. – Yaroslav Sep 24 '14 at 17:15
  • 1
    Awesome. Thank you very much. Appreciate all your help. – user48062 Sep 25 '14 at 14:08
  • Another question is how to stop the rest of the jobs ? – user48062 Sep 25 '14 at 14:14
  • You are now talking about a different thing, stopping jobs, not preventing steps inside of a job to execute. My advice, choose from the answers the one that solves your current question, check it as correct answer. Then create a new question with the new scenario, maybe using the comment you added on the other answer. – Yaroslav Sep 25 '14 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.