quick sp for todays jobs

Last Post 18 May 2007 06:26 AM by sqladmin. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqladmin
New Member
New Member

--
18 May 2007 06:26 AM
here's a quick stored procedure which will show
you a listing of today's job status.

the procedure is called: sp_jobs


create procedure sp_jobs
as
select distinct [name] as 'Job Name',
--***********************************************************
--***********************************************************
--Enabled or Disabled
case [enabled] when 1 then 'Enabled' else 'Disabled'
end as 'Enabled',
--***********************************************************
--***********************************************************
--Last Run Date & Time combined into the same column on output.
cast
(ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime) as 'Last Run',
--***********************************************************
--***********************************************************

--***********************************************************
--***********************************************************
--Last Run Status (Failed or Success)
case .[run_status]
when 0 then 'Failed' else 'Success'
end as 'Status' ,
--***********************************************************
--***********************************************************
--Last Run Duration HHMMSS Converted to 00:00:00
STUFF(STUFF(REPLACE(STR(run_duration,6),' ','0'),5,0,':'),3,0,':')
as 'Duration',
--***********************************************************
--***********************************************************
--Next Run Date & Time combined into the same column on output.
cast
(ltrim(str(next_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(next_run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime) as 'Next Run'
--***********************************************************
--***********************************************************
-- From Joined System Tables: Sysjobs, Sysjobschedules & Sysjobhistory.
from msdb.dbo.sysjobs j
left join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
join msdb.dbo.sysjobhistory h on j.job_id = h.job_id
where
cast (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')
as datetime) > dateadd(day, -1, getdate())
and step_id = 0
You are not authorized to post a reply.

Acceptable Use Policy