We help IT Professionals succeed at work.

Quey for list of SQL agent Jobs with scheduled (Sql server 2000)

Query for listing active SQL agent Jobs with SQL scheduled.
Comment
Watch Question

Commented:
SELECT job_id, [name] FROM msdb.dbo.sysjobs;

Author

Commented:
SELECT job_id, [name] FROM msdb.dbo.sysjobs doesn't return scheduled times

Commented:
you need to join with sysjobschedules to get details. here is an example that shows next run time.
WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND (SUBSTRING( CONVERT(VARCHAR(10),
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >
SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)

Open in new window

Author

Commented:
getting syntax error

Commented:
oops sql server 2000 the code was for cte based.

attaching sample code found on the net with attribution.
/*
  Created by Solihin Ho - http://solihinho.wordpress.com

  Usage : Change the value of variable @Filter
          'Y' --> display only enabled job
          'N' --> display only disabled job
          'A' --> display all job
          'X' --> display job which is duration already end
*/

DECLARE @Filter CHAR(1)
SET @Filter = 'A'

DECLARE @sql VARCHAR(8000)
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
BEGIN
    DROP TABLE #xp_results
END

CREATE TABLE #xp_results (
     job_id                UNIQUEIDENTIFIER NOT NULL,
     last_run_date         INT              NOT NULL,
     last_run_time         INT              NOT NULL,
     next_run_date         INT              NOT NULL,
     next_run_time         INT              NOT NULL,
     next_run_schedule_id  INT              NOT NULL,
     requested_to_run      INT              NOT NULL,
     request_source        INT              NOT NULL,
     request_source_id     sysname          COLLATE database_default NULL,
     running               INT              NOT NULL,
     current_step          INT              NOT NULL,
     current_retry_attempt INT              NOT NULL,
     job_state             INT              NOT NULL
)

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner	

SET @sql = '
SELECT
  j.Name AS JobName
, c.Name AS Category
, CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled
, CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled
, j.Description
, CASE s.freq_type
     WHEN  1 THEN ''Once''
     WHEN  4 THEN ''Daily''
     WHEN  8 THEN ''Weekly''
     WHEN 16 THEN ''Monthly''
     WHEN 32 THEN ''Monthly relative''
     WHEN 64 THEN ''When SQL Server Agent starts''
     WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs
, CASE s.freq_type
     WHEN  1 THEN ''O''
     WHEN  4 THEN ''Every ''
        + convert(varchar,s.freq_interval)
        + '' day(s)''
     WHEN  8 THEN ''Every ''
        + convert(varchar,s.freq_recurrence_factor)
        + '' weeks(s) on ''
        + master.dbo.fn_freq_interval_desc(s.freq_interval)
     WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval)
        + '' of every ''
        + convert(varchar,s.freq_recurrence_factor)
        + '' month(s)''
     WHEN 32 THEN ''The ''
        + CASE s.freq_relative_interval
            WHEN  1 THEN ''First''
            WHEN  2 THEN ''Second''
            WHEN  4 THEN ''Third''
            WHEN  8 THEN ''Fourth''
            WHEN 16 THEN ''Last'' END
        + CASE s.freq_interval
            WHEN  1 THEN '' Sunday''
            WHEN  2 THEN '' Monday''
            WHEN  3 THEN '' Tuesday''
            WHEN  4 THEN '' Wednesday''
            WHEN  5 THEN '' Thursday''
            WHEN  6 THEN '' Friday''
            WHEN  7 THEN '' Saturday''
            WHEN  8 THEN '' Day''
            WHEN  9 THEN '' Weekday''
            WHEN 10 THEN '' Weekend Day'' END
        + '' of every ''
        + convert(varchar,s.freq_recurrence_factor)
        + '' month(s)'' END AS Occurs_detail
, CASE s.freq_subday_type
     WHEN 1 THEN ''Occurs once at ''
        + master.dbo.fn_Time2Str(s.active_start_time)
     WHEN 2 THEN ''Occurs every ''
        + convert(varchar,s.freq_subday_interval)
        + '' Seconds(s) Starting at ''
        + master.dbo.fn_Time2Str(s.active_start_time)
        + '' ending at ''
        + master.dbo.fn_Time2Str(s.active_end_time)
     WHEN 4 THEN ''Occurs every ''
        + convert(varchar,s.freq_subday_interval)
        + '' Minute(s) Starting at ''
        + master.dbo.fn_Time2Str(s.active_start_time)
        + '' ending at ''
        + master.dbo.fn_Time2Str(s.active_end_time)
     WHEN 8 THEN ''Occurs every ''
        + convert(varchar,s.freq_subday_interval)
        + '' Hour(s) Starting at ''
        + master.dbo.fn_Time2Str(s.active_start_time)
        + '' ending at ''
        + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency
, CASE WHEN s.freq_type =  1 THEN ''On date: ''
          + master.dbo.fn_Date2Str(active_start_date)
          + '' At time: ''
          + master.dbo.fn_Time2Str(s.active_start_time)
       WHEN s.freq_type < 64 THEN ''Start date: ''
          + master.dbo.fn_Date2Str(s.active_start_date)
          + '' end date: ''
          + master.dbo.fn_Date2Str(s.active_end_date) END as Duration
, master.dbo.fn_Date2Str(xp.next_run_date) + '' ''
    + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date
FROM  msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules s (nolock) ON j.job_id = s.job_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id
WHERE 1 = 1
@Filter
ORDER BY j.name'

IF @Filter = 'Y'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ')
ELSE
IF @Filter = 'N'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ')
ELSE
IF @Filter = 'X'
   SET @sql = REPLACE(@sql,'@Filter',
                            'AND s.active_end_date < convert(varchar(8),GetDate(),112) ')
ELSE
   SET @sql = REPLACE(@sql,'@Filter','')

EXEC(@sql)

Open in new window

Commented:
you can join following tables:-
msdb.dbo.sysjobs
msdb.dbo.sysjobschedules

where enabled field in both the table is 1.

Author

Commented:
Invalid object name 'master.dbo.fn_freq_interval_desc'.
Invalid object name 'master.dbo.fn_Time2Str'.
Invalid object name 'master.dbo.fn_Date2Str'.
Commented:
try this instead. no additional functions needed. don't have 2000 here so unable to try these things.

select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name, 'None') Job_Owner,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
Inner join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name

Open in new window

Author

Commented:
in above query I replace sysschedules with sysjobschedules and looks great but I also need scheduled details like freq , time interval and scheduled days
Top Expert 2012

Commented:
You can use a combination of the system Stored Procedures sp_help_job and sp_help_jobschedule