[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

How to programmatically stop an executing Job from a within a step

I am new to creating/managing SQL Agent Jobs. I need to create a Job with multiple steps in which either the job must be stopped if a certain condition is meet during the first step processing. Is there a way to issue a command in a step that would make the job stop?

I have been spending quite a bit of time searching on the WEB and I can not find what I am looking for. Any help will be greatly appreciated.
0
fishbait01
Asked:
fishbait01
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Chris MangusDatabase AdministratorCommented:
If a step in a job fails you can trap that and stop the execution of a job.  I don't know of a way to stop a job because of a condition in the code.

Perhaps you could create 2 jobs and start the second job from the first only if your condition is not met.
0
 
fishbait01Author Commented:
Can programmatically make a step fail. Is there such a command or trick to accomplish this?
0
 
WizillingCommented:
i think you can achieve what u are trying through sp_stop_job

sp_stop_job
      [@job_name =] 'job_name'
    | [@job_id =] job_id
    | [@originating_server =] 'master_server'
    | [@server_name =] 'target_server'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris MangusDatabase AdministratorCommented:
Wizilling's idea is certainly something to experiment with.  I wonder what the effects are of having a job call sp_stop_job to stop itself...
0
 
Chris MangusDatabase AdministratorCommented:
I found a couple references that indicate it is safe to use sp_stop_job within a job to stop itself.

Neat technique, wizilling...
0
 
WizillingCommented:
Glad to help...
0
 
fishbait01Author Commented:
I'll test this out in the morning and let you know. Thank you all for your help.
0
 
nmcdermaidCommented:
You can indicate that the first step should 'complete reporting success' if the first step fails.

The downside is that if it really does fail, you don't know (unless you investigate the logs)
The upside is that you don't have to use sp_stop_job.

What kind of step is the first one? cmd line, SQL?
0
 
fishbait01Author Commented:
The first step is SQL. This job can execute for multiple sites or for corporate. When executing for a site (site job), it only uses the site's data and mapping tables wherease when executing for corporate (corporate job) will use all sites data and all mapping tables. So site jobs can execute concurrently but corporate requires exclusivity. I need to make sure a site can't submit multiple instance of the site job or that it can't submit if corporate job is executing (and vice versa). We have 2 different UI used by many users in different world regions and languages that all execute this job. Currently it's 'dumb' and we have concurrency issues. Also, I only have source code access to one of them so I'm trying to manage it at the SQL Server side. Not best practive but I need to be practicle and find a quick but robust solution.
0
 
nmcdermaidCommented:
In DTS you can use scripts to disable step and stop the DTS running. All of the steps you can do in a job, you can also do in a DTS. If you migrated it to a DTS you could that stuff, though its understandable if you don't want the hassle.
0
 
fishbait01Author Commented:
I have tested the sp_stop_job and it works great. Thanks Wizilling and everybody else.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now