Stop job as failure

Question

Stop job as failure

whenriksen

SSCarpal Tunnel

Points: 4752
More actions
March 20, 2014 at 5:00 pm

#283652

I need to stop a SQL Agent job with failure rather than cancelled. The stop request will come from an external script. I didn't see any option in sp_stop_job to stop with failure.
Alternatively, I need to kill a SSIS package started by a SQL Agent step and ensure the step fails. I've tried killing DTExec with Powershell, but the SQL Agent step that started DTExec returns success.
Any ideas?
Wes
(A solid design is always preferable to a creative workaround)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

I have to ask "why" you would want to do this?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

SQLBuddy123

One Orange Chip

Points: 28294

March 20, 2014 at 10:07 pm

#1698938

Yeah, Why would you do that ..

--

SQLBuddy

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

SQL Agent job if you cancelled than it will show as cancelled only. If it failed it will show as failure. Now if you would like to have cancelled as failure than it will be difficult to distinguish between failure and cancelled. Microsoft has applied rational logic behind this; can you justify why you are looking for alternet?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 4

We are using a multi-platform enterprise job monitoring application that we use for call-out support. The SQL Agent job is for a scheduled load from a critical internal application. If the load runs long, I need to stop it and return a failure to the job monitor so that a call-out occurs.

The enterprise job monitoring system does not see a cancellation as a failure (just like SQL Server) so I need to cause the job to return a failure if it needs to be cancelled.

Wes
(A solid design is always preferable to a creative workaround)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

The only thing I can think of is to stop the job and update the status in msdb to reflect an error, but how that would actually work with the job monitor I don't know (depends on how the monitor is polling the status of a job?)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 6

Assuming no better option, I'm considering adding another step to the job that checks the results of the first step load, which would be incomplete if killed. If the load did not complete, I'll have the new second step throw an error.

I was hoping there was a more straight-forward approach using just TSQL.

Thanks for the feedback.

Wes
(A solid design is always preferable to a creative workaround)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

While I don't recommend or endorse this, you could add a job step that has a SELECT 1/0 in it (that never actually gets run, the prior step would report job as success), create a trigger on the sysjob activity or sysjobs table to monitor for the last step of the job you're stopping, then when the trigger fires, it could execute the final step of the same job (which will fail due to divide by zero), and the end result would be a job failure.

Again, not an advisable plan, but could work...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

whenriksen (3/20/2014)
I need to stop a SQL Agent job with failure rather than cancelled. The stop request will come from an external script. I didn't see any option in sp_stop_job to stop with failure.
Alternatively, I need to kill a SSIS package started by a SQL Agent step and ensure the step fails. I've tried killing DTExec with Powershell, but the SQL Agent step that started DTExec returns success.
Any ideas?

From the external script after you have killed the job , add a SQL for creating a new Job step for that job using sp_add_jobstep and in the command section give a query with wrong syntax. So the Job step fails and job gets reported as failure .. some wrong SQL like

Select , @@$ServerNameee

--

SQLBuddy

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 9

free_mascot (3/20/2014)
SQL Agent job if you cancelled than it will show as cancelled only. If it failed it will show as failure. Now if you would like to have cancelled as failure than it will be difficult to distinguish between failure and cancelled. Microsoft has applied rational logic behind this; can you justify why you are looking for alternet?

Could you not use something similar to what we do. We have a job which runs every hour which identifies any long running jobs. When they are identified an email is sent out to highlight the long running job(s). This could be adapted to fail the job looking for anything long running which would show in your management app and trigger the call out?

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 10

You can place a condition inside of your T*SQL code : RAISERROR(...).