msdb in SQL Server 2005

• ksfok

  SSC Eights!

  Points: 949

  More actions

  November 6, 2006 at 6:41 pm

  #174838

  As msdb in SQL2005 is no longer the same as in SQL2000, what would be the 2005 version for the following SQL2000 query:

  SELECT b.name as job_name,c.Schedule,c.next_run_time

        ,step_name,subsystem,command,database_name

   FROM msdb..sysjobsteps a

  INNER JOIN (SELECT name,job_id

                  FROM msdb..sysjobs

                  WHERE enabled = 1)b

        ON a.job_id =b.job_id

  INNER JOIN (SELECT job_id

                    ,(CASE WHEN freq_type = 4 THEN 'Daily'

                           WHEN freq_type =16 THEN 'Monthly'

                           WHEN freq_type =8  THEN 'Weekly'

                           END )as Schedule

                    ,left(right('000000'+convert(varchar,next_run_time),6),4) as next_run_time

                FROM msdb..sysjobschedules ) c

       ON a.job_id =c.job_id

   

• joachim.verhagen

  Hall of Fame

  Points: 3560

  More actions

  November 7, 2006 at 2:30 am

  #670241

  You will need to join with both sysjobschedules and sysschedules.

  Or just be lazy and use sp_help_schedule