How to know a Job Status from Stored Procedure

[Resant]

To execute a job from VB, I use stored procedure with T-SQL :

EXEC sp_start_job @job_name = 'DTSName'
[color=blue]
>From BOL, I know that procedure return 0 (success) or 1 (failure), but[/color]
that's only to tell user that job successfully started, right?
What I need is how to know is that job have executed successfully or
failed, so if success i will execute another code.


Please help me, thanks

[Simon Hayes]

Re: How to know a Job Status from Stored Procedure

See sp_help_job in Books Online. Alternatively, you could use SQL-DMO
from VB to get the CurrentRunStatu s and/or LastRunOutcome property of
the Job object. Another option would be to add more steps to the job
itself, so that if the first step fails, it runs your extra code.

Simon

[Resant]

Re: How to know a Job Status from Stored Procedure

But when I have execute sp_help_job if i don't know the job was
finished or not?

[Simon Hayes]

Re: How to know a Job Status from Stored Procedure

The current_executi on_status column tells you if the job is running;
the last_run_outcom e columns tells you what happened the last time the
job finished.

If you want something to happen only when the job succeeds (or fails),
then it might be easiest to add another job step, as I mentioned. Then
you don't have to keep checking the job status from the client.

Simon