Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How do I get Job Final Status in today's date

Unanswered How do I get Job Final Status in today's date

  • Tuesday, February 12, 2013 5:38 AM
     
     
    Sign In to Vote
    0
    I have a job actually It is having 12 different steps.In couple of steps It gets retries because it has to wait for steps has to be performed by other before it moves further.So Every 5 minutes it checks that other job is completed then it moves further
    So due to that in Msdb.dbo.sysjobhistory table I have run_status=2 for my retried steps.

    Now I want logic like this

    In today's date whether job is completed successfully or not

    For that I have written my query like below

    select name,max(run_status) from msdb.dbo.sysjobs j
    inner join msdb.dbo.sysjobhistory h
    on j.job_id=h.job_id
    where j.name in ('Full Process 1')
    and convert(varchar(8),getdate(),112)=h.run_date
    group by name


    I concern with job output in today's date like sucess or failure I don't oncern which steps takes how many retries

    Please help me for the same
     

All Replies

  • Tuesday, February 12, 2013 5:51 AM
     
     
    Sign In to Vote
    0

    I got the solution like this

    select name,run_statusfrom msdb.dbo.sysjobs j
    inner join msdb.dbo.sysjobhistory h
    on j.job_id=h.job_id
    where j.name in ('Full Process 1')
    and convert(varchar(8),getdate(),112)=h.run_date
    and step_id=0

    • Marked As Answer by Aminesh Tuesday, February 12, 2013 5:51 AM
    • Unmarked As Answer by Aminesh Tuesday, February 12, 2013 5:58 AM
    •  
     
  • Tuesday, February 12, 2013 5:54 AM
     
     
    Sign In to Vote
    0

    Total I have 8 jobs

    like Full Process 1,Full Process 2......................Full Process 8

    So above query loooks like this

    select name,run_statusfrom msdb.dbo.sysjobs j
    inner join msdb.dbo.sysjobhistory h
    on j.job_id=h.job_id
    where j.name in ('Full Process 1','Full Process 2','Full Processs 3','Full Process 4'...................)
    and convert(varchar(8),getdate(),112)=h.run_date
    and step_id=0

    Now If All of them return 1 then set @x=0 else set @x=1

     
  • Tuesday, February 12, 2013 2:35 PM
    Moderator
     
      Has Code
    Sign In to Vote
    1

    Try

    ;with JobsRunToday as (select name,run_statusfrom msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobhistory h
on j.job_id=h.job_id
where j.name in ('Full Process 1','Full Process 2','Full Processs 3','Full Process 4'...................)
and convert(varchar(8),getdate(),112)=h.run_date
and step_id=0)

select @x = case when max(run_Status) = min(run_Status)
and max(run_Status) = 1 then 0 else 1 end
from JobsRunToday

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, February 21, 2013 12:35 AM
    Moderator
     
     
    Sign In to Vote
    0

    Any progress?

    Job related queries:

    http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012