Returning a success or failure code within a job

Question

Returning a success or failure code within a job

Carl Rimmer

SSCarpal Tunnel

Points: 4068
More actions
June 29, 2006 at 4:06 am

#113559

I am trying to pass a success or failure code within a job to enable the workflow of a second step of the job on the outcome of the failure code.
What I am trying to do is check the sysjobhistory table for the run_status of a job using TSQL and return a code from the SQL that makes the Job quit with failure or run the next step with success.
Any help would be appreciated.

Carl

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Carl,

I'm not sure what you're trying to do but why can't you use the job's very own success/failure flow logic? Or are you trying to interrogate a job's state outside of that job?

Either way, you cannot rely on sysjobhistory for this information because it isn't populated until after the job is has completed.

You'd need to use the extended proc xp_sqlagent_enum_jobs. Check out this article which goes into a bit more detail.

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

By the way the values for the State column are:

0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions

Hope that helps,

Karl
source control for SQL Server

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

If the Job is using a stored procedure, you could use the RETURN (0) for success and RETURN (1) for failure in the stored procedure.

Don't forget the (), T-SQL is hinky that way.

Andy

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 3

Thanks Guys,

What I'm trying to do in plain English is check for the success of an Overnight job that loads the data into the tables before running the subsequent reports agains the tables populated by the load Job.

I want to check that the Overnight has succeeded before running the morning reports that use the data.

Hope this clears things up?

PS I have written a DTS package that checks for the existence of a file that could be written after the Overnight Job has run to check the file exists and it has todays date on it. But I feel that is a bit fiddly. I'd sooner do it within the Job via a piece of SQL.

Carl

SQLZ SSChampion Points: 12872 More actions · Answer 4

Hi Carl,

In that case I think you can get away with just check the sysjobservers table.

You want to check that the last_run_outcome is 1 (ignore BOL in this case, it's wrong) and you want to also check that the last_run_date was today. Note that the last_run_date are stored in integer format so you'll need to convert it (e.g. 20060629 = 29 Jun 2006). You'll face a similar problem with the last_run_time but you might not need to use it.

So I guess you'll want something like this.

if exists (select 1 from msdb..sysjobservers s join msdb..sysjobs j on j.job_id = s.job_id where j.name = 'the name of the job' and last_run_outcome = 1 and last_run_date = CONVERT(INT,CONVERT(VARCHAR(8),GETDATE(),112)))

begin

--the job succeeded today so do whatever needs to be done

end

else

begin

--the job failed so raise an error or handle appropriately

end

Hope that helps,

Karl
source control for SQL Server

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 5

Karl,

I'll give that a try.

Thanks for your reply.

Carl

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 6

Karl,

That code works a treat. I understand what you mean about ignoring BOL.

I'll need to run this code as the first step in every job that we have.

I've got the code sending a RETURN when successful and a RAISERROR when it fails.

Carl