Results 1 to 5 of 5

Thread: Retriving latest job from msdb..sysjobhistory

  1. #1
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    29

    Retriving latest job from msdb..sysjobhistory

    Hi,
    Can any one suggest me how to retrieve most recent job from msdb..sysjobhistory table?

    I want to supply the job name which has more than 1 steps. Step 1 or more is already completed ( success/failure) and in the last step I am trying to retrieve sysjobhistory.messages(success/failure) stored in the sysjobhistory table for the steps already executed.
    I want the records related with last/current job executed.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Why don't you order by run_date and run_time

    select * from msdb..sysjobhistory
    order by run_date, run_time desc

  3. #3
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    29
    Hi,
    Actually this is a step forward in the direction of using SMTP mail server for informing job status.
    Hopw you guys now undestand what I am trying to do. My idea is I am trying to add one diagostic step in every job, which will collect all the information (failure/success) about the running job and its steps. After collecting I will build the e-mail body and send them using smtp e-mail.

  4. #4
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    29
    Hi,
    I only want the latest specific job. When I try to retrieve a specific job, I get old information about that job.
    I can use TOP 1 or TOP2. But for different jobs number of steps are different, in that case I have to modify that for every job and if in future we add another step in the job, I have to modify this code also.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Use this to get the latest job result

    select *
    from msdb..sysjobhistory sh
    join (select max(instance_id) from msdb..sysjobhistory group by job_id) as s
    on s.instance_id = sh.instance_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •