Tuesday 8 May 2012

SQL Server Failed jobs in SSMS


The following sql will list all the jobs that have failed in sql server:

SELECT TOP 100 PERCENT      

Z.Originating_Server,
     msdb.dbo.sysjobhistory.message as Error_Message,
     len(msdb.dbo.sysjobhistory.message) as Error_Len
,    msdb.dbo.sysjobs.name AS Job_Name
    , msdb.dbo.sysjobhistory.step_id AS Step
    , msdb.dbo.sysjobhistory.step_name AS Job_Step_Name
    , (CASE
     WHEN  msdb.dbo.sysjobhistory.run_status = 0 THEN 'Failed'
     WHEN  msdb.dbo.sysjobhistory.run_status = 1  THEN 'Succeeded'
     WHEN  msdb.dbo.sysjobhistory.run_status = 2  THEN 'Retry'
     WHEN  msdb.dbo.sysjobhistory.run_status = 3  THEN 'Cancelled'
     WHEN  msdb.dbo.sysjobhistory.run_status = 4  THEN 'In progress'
    END)  AS Status
    , cast(
                   cast(msdb.dbo.sysjobhistory.run_date as varchar) + ' ' +
                  left(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 2) + ':' +
                  substring(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 3, 2) + ':' +
                 right(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6),2)
                  as smallDatetime)  AS Date
   
     , (SELECT DATENAME (dw,  (select convert (smalldatetime, convert (varchar(10), msdb.dbo.sysjobhistory.run_date))))) AS [Day]
   
    , (CASE
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 6 THEN substring(cast(run_duration as char(6)),1,2)+':'+substring(cast(run_duration as char(6)),3,2)+':'+substring(cast(run_duration as char(6)),5,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 5 THEN '0' +substring(cast(run_duration as char(5)),1,1)+':'+substring(cast(run_duration as char(5)),2,2)+':'+substring(cast(run_duration as char(5)),4,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 4 THEN '00'+':'+substring(cast(run_duration as char(4)),1,2)+':'+substring(cast(run_duration as char(4)),3,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 3 THEN '00:0'+substring(cast(run_duration as char(3)),1,1)+':'+substring(cast(run_duration as char(3)),2,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 2 THEN '00:00'+':'+substring(cast(run_duration as char(2)),1,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 1 THEN '00:00:0'+substring(cast(run_duration as char(1)),1,1)
     ELSE '00:00:00'
    END)   AS [Duration]
    , msdb.dbo.syscategories.name AS Job_Category

FROM msdb.dbo.sysjobs LEFT OUTER  JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
join  msdb.dbo.sysoriginatingservers_view  Z on z.originating_server_id = msdb.dbo.sysjobs.originating_server_id
WHERE
cast(
            cast(msdb.dbo.sysjobhistory.run_date as varchar) + ' ' +
            left(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 2) + ':' +
            substring(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 3, 2) + ':' +
            right(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6),2)
            as smallDatetime)  > DATEADD(dd, - 1, current_timestamp )
AND  (msdb.dbo.sysjobhistory.step_id > 0)
AND  (msdb.dbo.sysjobhistory.run_status <>1)
ORDER BY
[Date] DESC

CTEs


For Dates in a calendar style report you could use the following to generate the dates.

   with mycte as

   ( select cast('2012-01-01' as datetime) DateValue

        union all

        select DateValue + 1

        from    mycte  

         where   DateValue + 1 < getdate())

   -- select DateValue

   -- from    mycte

   --OPTION (MAXRECURSION 0)

select DateValue,

Year(DateValue) as Year,

DATEPART(Quarter ,DateValue) as Quarter,

DATENAME( month,DateValue) as MonthName,

DATEPART(m ,DateValue)  as MonthNo,

day (DateValue) as Day

from    mycte

OPTION (MAXRECURSION 0)

Keep SSRS (SSRS2016) report manager awake

When running a report for the first time in report manager it takes a while to run, after this initial run reports run fine.  There are a ...