Data World

Contact Me

If you want to contact me for whatever reason then fill in the form and I will contact you ASAP!


Advertisements

2 Responses to “Contact Me”

  1. Shehbaz Hussain said

    Dear Sir,
    I want write a query I can run against a database for SQL Server 2005/2008 that will gather the following details from the scheduled tasks:

    Backup – Last Date-Time Ran, Duration, Status OK/Error Occured
    Integrity – Last Date-Time Ran, Duration, Status OK/Error Occured
    Optimization – Last Date-Time Ran, Duration, Status OK/Error Occured

    These may be run each query separately for it each scheduled task, I just don’t what to implement a UDF if that is at all possible.

    Kindly help me sir.

    Thank you.

    • SQLAnidma said

      Hopeflly this is what you are looking for

      ;WITH cte AS (SELECT j.name,
      Max(h.instance_id) instance_id
      FROM msdb.dbo.sysjobhistory h
      LEFT JOIN msdb.dbo.sysjobs j
      ON h.job_id = j.job_id
      GROUP BY j.name
      )
      —–
      SELECT c.name,
      h.run_date,
      h.run_time,
      h.run_duration [run_duration(hh:mm:ss)],
      CASE h.run_status
      WHEN 0 THEN ‘Failed’
      WHEN 1 THEN ‘Success’
      WHEN 2 THEN ‘Retry’
      WHEN 3 THEN ‘Canceled’
      ELSE ‘Unkown’
      END status
      FROM cte c
      LEFT JOIN msdb.dbo.sysjobhistory h
      ON h.instance_id = c.instance_id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: