About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Sunday, February 21, 2016

vwJob - no dependencies

I've been asked to produce the code for vwJob, vwJobStep, vwJobSched, and vwJobHistory with no dependencies.

This is created in tempdb but you can optionally put it into your Admin (or similarly named) database.

Use this for an easy method to retrieve metadata about jobs.


/*    DATE        AUTHOR            REMARKS
      2/21/16     PPaiva            Initial creation.


      USAGE
            -- Jobs
            SELECT *
            FROM vwJob
            ORDER BY Name

            -- Jobs with Steps
            -- Column Command is limited to 300 chars, but you may wish to remove the limit
            SELECT Instance, JobEn, Name, Description, Category, StepID, StepName, Subsystem, Filename, Left(Command, 300) Command, PathFile, JobCreated, JobModified, Job_ID, step_uid
            FROM vwJobStep
            ORDER BY Name, StepID

            -- Jobs with Schedules
            SELECT *
            FROM vwJobSched
            ORDER BY OrderByMe

            -- Job History
            SELECT *
            FROM vwJobHistory
            ORDER BY instance_id desc    


      DEPENDENCIES (contained within)
            udfFormatJobNextRunDate()
            udfGetFilenameOnly()
            vwJob
            

*/
Use tempdb
-- Use Admin      -- Put this in your own database if you have one
go

IF object_id('dbo.udfFormatJobNextRunDate') Is Not Null
      DROP FUNCTION dbo.udfFormatJobNextRunDate
go

CREATE FUNCTION dbo.udfFormatJobNextRunDate(
      @NextRunDateTEXT varchar(8) = '',
      @NextRunTimeTEXT varchar(6)
      )
RETURNS Datetime
AS
/*  DATE        AUTHOR          REMARKS
    2/21/16     PPaiva          Initial creation


      DESCRIPTION
            Formats the text columns Next_Run_Date and Next_Run_Time
                        in sysJobSchedules to a datetime format.

            TIME formatting
                        Input:  4000
                        Output:  00:40:00

      USAGE
            SELECT  *,
                  dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) ProperlyFormattedDatetime
            FROM msdb.dbo.sysJobSchedules
            ORDER BY len(next_run_time)
          
            SELECT dbo.udfFormatJobNextRunDate('', '3')
            SELECT dbo.udfFormatJobNextRunDate('20160401', '3')
            SELECT dbo.udfFormatJobNextRunDate('20160401', '2300')
            SELECT dbo.udfFormatJobNextRunDate('20160401', '12300')
            SELECT dbo.udfFormatJobNextRunDate('20160401', '112300')

*/
BEGIN
      DECLARE @Out datetime,
                  @NextRunTimeWithColons varchar(8),
                  @LenTime int
    
                
      -- Pad left with zeros
      SET @LenTime = Len(@NextRunTimeTEXT)
      SET @NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT


      -- Add colons.  Change 005000 to 00:50:00
      SET @NextRunTimeWithColons = Left(@NextRunTimeTEXT, 2)
                        + ':'
                        +     Substring(@NextRunTimeTEXT, 3, 2)
                        + ':'
                        +     Right(@NextRunTimeTEXT, 2)

      IF @NextRunDateTEXT = ''
            SET @Out = @NextRunTimeWithColons
                             
      ELSE
            -- Concatenate date and time
            SET @Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null
                                          ELSE Convert(datetime, @NextRunDateTEXT + ' ' + @NextRunTimeWithColons
                                                                  )
                                                END


RETURN @Out

END
go

IF object_id('dbo.udfGetFilenameOnly') Is Not Null
      DROP FUNCTION dbo.udfGetFilenameOnly
go

CREATE FUNCTION dbo.udfGetFilenameOnly(
      @In varchar(500)
      )
RETURNS varchar(300)
AS
/*    DATE        AUTHOR            REMARKS
      8/19/15     PPaiva            Initial creation.


      USAGE
            SELECT dbo.udfGetFilenameOnly('D:\Program Files\Microsoft SQL Server\Logs\Admin_Log.LDF')
            SELECT dbo.udfGetFilenameOnly('Admin_Log.ldf')

*/

BEGIN
      DECLARE @Out varchar(300),
                  @Pos smallint
           
      -- Get position of last backslash
      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1
            BEGIN
                  SET @Pos = Len(@In) - @Pos               
                  SET @Out = Substring(@In, @Pos + 2, 500)
            END
     
      ELSE
            SET @Out = @In


      RETURN @Out

END
go

IF object_id('dbo.vwJob') Is Not Null
      DROP VIEW dbo.vwJob
go

CREATE VIEW dbo.vwJob
AS
/*  DATE        AUTHOR      REMARKS
    2/21/16     PPaiva      Initial creation.
     
         
      DESCRIPTION
            Returns list of jobs.
            Column SchedEn looks at all schedules for a job.  If any one is
                  enabled, then SchedEn = 1.

      USAGE
            SELECT *
            FROM vwJob
            ORDER BY Instance, JobEn desc, Name

*/
WITH cteJobNextRunSchedEn
AS (
      SELECT  job_id,
                  Min(Convert(varchar(16), dbo.udfFormatJobNextRunDate(next_run_date, next_run_time), 120)) NextRunDatetime,
                  CASE WHEN Sum(Convert(int, sched.enabled)) > 0
                              THEN 1
                         ELSE 0
                         END SchedEn,
                  Count(*) NumSched
      FROM msdb.dbo.sysjobschedules jobsched
      JOIN msdb.dbo.sysschedules sched
            ON sched.schedule_id = jobsched.schedule_id
      GROUP BY job_id
      )
SELECT  Convert(varchar(50), ServerProperty('ServerName')) Instance,
        J.Enabled JobEn,
            SchedEn.SchedEn,
            IsNull(SchedEn.NumSched, 0) NumSched,
        J.Name,
        J.Description,  
            SchedEn.NextRunDateTime,
        CASE WHEN C.Name = '[Uncategorized (Local)]' THEN ''
                        ELSE c.Name END  Category,
           
--          Originating_Server OrigServer,
            msdb.dbo.SQLAGENT_SUSER_SNAME(owner_sid) Owner,
            J.Category_ID,
        J.Date_Created JobCreated,
        J.Date_Modified JobModified,
        J.Job_ID
FROM msdb.dbo.sysJobs J
LEFT JOIN msdb.dbo.syscategories C
      ON C.Category_ID = J.Category_ID
LEFT JOIN cteJobNextRunSchedEn SchedEn
      ON SchedEn.job_id = j.job_id

go

IF object_id('dbo.vwJobStep') Is Not Null
      DROP VIEW dbo.vwJobStep
go

CREATE VIEW dbo.vwJobStep
AS
/*    DATE        AUTHOR            REMARKS
      2/21/16     PPaiva            Initial creation.


      SELECT *
      FROM vwJob

      SELECT *
      FROM vwJobStep
      WHERE Name like '%%'
      ORDER BY Name, StepID

      SELECT *
      FROM vwJobStep
      WHERE Name = '%%'
      ORDER BY Name, StepID

      SELECT *
      FROM msdb.dbo.sysjobsteps s

*/
WITH cte AS (    
      SELECT  j.Instance,
                  j.JobEn,
                  j.Name,
                  j.Category,
                  j.Description,
                  s.step_id StepID,
                  s.step_name StepName,
                  s.Subsystem,
                  s.Command,
                  CharIndex('dtexec.exe', Command) PosDTExec,
                  CharIndex('"', Command) PosDoubleQuote1,
                  CharIndex('"', Command, CharIndex('"', Command) + 1) PosDoubleQuote2,
                  CharIndex('php.exe', Command) PosPHP,
                  CharIndex(' ', Command) PosSpace1,
                  j.JobCreated,
                  j.JobModified,
                  j.Job_ID,
                  s.step_uid
      FROM vwJob j
      LEFT JOIN msdb.dbo.sysjobsteps s
            ON s.job_id = j.job_id
      ),
cte2 AS (
      SELECT  Instance, JobEn, Name, Category, Description, StepID, StepName,
                  Subsystem,
                  PosPHP,
                  CASE WHEN PosDoubleQuote1 > 0 AND Subsystem In( 'CmdExec', 'SSIS')
                        THEN Substring(Command, PosDoubleQuote1 + 1, PosDoubleQuote2 - PosDoubleQuote1 - 1)
                        ELSE '' END PathFileSSIS,
                  CASE WHEN PosPHP > 0
                        THEN Substring(Command, PosSpace1, 1000)
                        ELSE '' END PathFilePHP,
                  Command,
                  JobCreated, JobModified, Job_ID, step_uid
      FROM cte
      ),
cte3 AS (
      SELECT  Instance, JobEn, Name, Category, Description, StepID, StepName,
                  CASE WHEN Len(PathFileSSIS) > 0 AND PosPHP = 0 AND SubSystem = 'CmdExec'
                              THEN Subsystem + ' SSIS'
                         WHEN Len(PathFileSSIS) > 0 AND PosPHP = 0 AND SubSystem = 'SSIS'
                              THEN Subsystem
                         WHEN Len(PathFilePHP) > 0 AND PosPHP > 0
                              THEN Subsystem + ' PHP'
                        ELSE Subsystem END Subsystem,
                  Command,
                  PathFileSSIS,
                  PathFilePHP,
                  JobCreated, JobModified, Job_ID, step_uid
      FROM cte2
      ),
cte4 AS (
      SELECT  Instance, JobEn, Name, Category, Description, StepID, StepName,
                  Subsystem,
                  Command,
                  CASE WHEN Len(PathFileSSIS) > 0
                              THEN PathFileSSIS
                         WHEN Len(PathFilePHP) > 0
                              THEN PathFilePHP
                         WHEN Subsystem = 'CmdExec'
                              THEN Command
                        ELSE '' END PathFile,
                  JobCreated, JobModified, Job_ID, step_uid
      FROM cte3
      )
SELECT  Instance, JobEn, Name, Description, Category, StepID, StepName,
            Subsystem,
            dbo.udfGetFilenameOnly(PathFile) Filename,
            Command,
            PathFile,
            JobCreated, JobModified, Job_ID,
            step_uid
FROM cte4


go
IF object_id('dbo.vwJobSched') Is Not Null
      DROP VIEW dbo.vwJobSched

go
CREATE VIEW dbo.vwJobSched
AS
/*  DATE        AUTHOR          REMARKS
    2/21/16     PPaiva          Initial creation.
     
   
      DESCRIPTION
            NOTE that since this JOINs to sysJobSchedules, this view
            does not necessarliy show a distinct list of Job Name.

      DEPENDENCIES
            dbo.udfFormatJobNextRunDate()

      USAGE
            -- The next job to be executed is shown at the top
            SELECT *
            FROM vwJobSched
            WHERE Name Like '%%'
            ORDER BY Instance, JobEn desc, SchedEn desc, NextRunDateTime

            SELECT *
            FROM vwJobSched
            ORDER BY Instance, JobEn desc, SchedEn desc, OrderByMe, NextRunDateTime

      DEBUG
            SELECT *
            FROM msdb.dbo.sysJobSchedules

*/
WITH cte
AS
(
SELECT  Convert(varchar(50), ServerProperty('ServerName')) Instance,
            J.Enabled JobEn,
            ss.Enabled SchedEn,
            Convert(varchar(16), dbo.udfFormatJobNextRunDate(js.next_run_date, js.next_run_time), 120) NextRunDatetime,
                  J.Name,
            J.Description,  
            CASE WHEN C.Name = '[Uncategorized (Local)]' THEN ''
                              ELSE c.Name END  Category,
            ss.Name SchedName,
--          Originating_Server OrigServer,
            JS.Next_Run_Date,
            JS.Next_Run_Time,
            J.Date_Created JobCreated,
            J.Date_Modified JobModified,
                  js.schedule_id,
            J.Job_ID
FROM msdb.dbo.sysJobs J
LEFT JOIN msdb.dbo.sysJobSchedules JS
      ON JS.Job_ID = J.Job_ID
LEFT JOIN msdb.dbo.syscategories C
      ON C.Category_ID = J.Category_ID
LEFT JOIN msdb.dbo.sysschedules ss
      ON ss.schedule_ID = js.schedule_ID
)

SELECT CASE WHEN NextRunDateTime Is Null
                        THEN 'Null'
                   WHEN NextRunDateTime < GetDate()
                        THEN 'Past'
                  ELSE 'Future'
            END NextRun,
            *,
            CASE WHEN NextRunDateTime Is Null
                        THEN '2200-01-01'
                   WHEN NextRunDateTime < GetDate()
                        THEN '2100-01-01'
                  ELSE NextRunDateTime
            END OrderByMe
FROM cte

go
IF object_id('dbo.vwJobHistory') Is Not Null
      DROP VIEW dbo.vwJobHistory

go

CREATE VIEW dbo.vwJobHistory
AS
/*  DATE        AUTHOR          REMARKS
    2/21/16     PPaiva          Initial creation.


      DEPENDENCY
            dbo.udfFormatJobNextRunDate()
           

      -- Oldest job         
      SELECT Min(RunDatetime)
      FROM vwJobHistory

      SELECT *
      FROM vwJobHistory
      ORDER BY RunDateTime desc

      -- Most recent failed jobs
      SELECT *
      FROM vwJobHistory
--    WHERE Status <> 'Succeeded'
      ORDER BY RunDateTime desc

*/

SELECT  Convert(varchar(50), ServerProperty('ServerName')) Instance,
            CASE WHEN jh.Run_Status = 0 THEN 'Failed'
                  WHEN Run_Status = 1 THEN 'Succeeded'
                  WHEN Run_Status = 2 THEN 'Retry'
                  WHEN Run_Status = 3 THEN 'Canceled'
                  WHEN Run_Status = 4 THEN 'In Progress'  
                  ELSE 'Undefined Status in View'
            END Status,
            j.Name,
                  CASE WHEN c.name = '[Uncategorized (Local)]' THEN ''
                              ELSE c.name END Category,
            --Run_Date,
            --Run_Time,
                  Convert(varchar(16), dbo.udfFormatJobNextRunDate(run_date, run_time), 120) RunDatetime,      -- Use this for HH:MM,
                  -- dbo.udfFormatJobNextRunDate(run_date, run_time) RunDatetime,            -- Use this for HH:MM:SS.NNN
                  --dbo.udfFormatJobNextRunDate('', CASE WHEN run_duration > 0 then run_duration ELSE '0' END) RunDuration,  Use this for HH:MM:SS.NNN
                  -- This is protected against negative numbers which can happen when dayligh savings ends
                  Convert(varchar(19), dbo.udfFormatJobNextRunDate('', CASE WHEN run_duration > 0 then run_duration ELSE '0' END), 120) RunDuration,   -- Use this for HH:MM:SS,
            step_id, step_name, server,
            message,
            jh.job_id,
            instance_id
            --sql_message_id, sql_severity, run_status, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted
FROM msdb.dbo.sysJobHistory jh
JOIN msdb.dbo.sysJobs j
      ON j.job_id = jh.job_id
LEFT JOIN msdb.dbo.syscategories C
      ON C.Category_ID = j.Category_ID



go

SELECT *
FROM vwJob
ORDER BY Name