November 6, 2006 at 6:41 pm
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
November 7, 2006 at 2:30 am
You will need to join with both sysjobschedules and sysschedules.
Or just be lazy and use sp_help_schedule
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply