Checking Job Status using T-SQL

Hi.

How can i check the job status using SQL Query?

The following query may help you...

Select
*
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
Name='Your Schd. Package Name'
Order By
Run_Date Desc,
run_time Desc

|||Look up using [sp_help_job] in Books Online.|||

by the way....

What is meant by JOB STATUS ?

|||

Raja, I meant If the job (last execution/historical) executed successfully or not..From the above query..

KangKang, use RUN_Status column to find the status

1=Executed Successfuly

0=Failed with error

When Failed you can find the details from the Message Column

|||

I get too many recrod when i use this method

Select
DISTINCT run_status
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
name = 'STR Balance'

I just want to get the current run_stauts and store it in a variable. I have refer to SP_Help_Job but i do not know how can i return the run_Status

|||

actually i just want to return the current status, i tried but it return the new and old execute status.

|||I tryto change the SP_HELP_JOB but its too complicated, Is there any other method so i can insert all things into temp table and select current_status from the temp table.|||

I guess I don't understand why you think sp_help_job is so complicated.

You give it a job name, and it gives you a status. Actually, very simple.

|||

YA actually is very easy.....I should try more before post...:).

|||

The problem with using sp_help_job in a batch is that you can't return the results to a table. This is because sp_help_job itself returns results to a table as part of its processing, and

An INSERT EXEC statement cannot be nested.

|||

I have modify the sp_help_job and the sp_get_composite_job_info to retrun only 1 value.

:)