February 29, 2012 at 8:29 am
I want to give a 'date' to the stored procedure as a input parameter and that sp should return me the following:
(1) The list of jobs that are scheduled to run on that given date
(2) Whether those jobs ran or not
(3) If they did ran, what was the run_time, run_duration, run_status and run_message
February 29, 2012 at 8:38 am
Since there is no question here, I'm guessing that you want someone to write the stored procedure for you? That's not generally the way it works. Please have a go at writing it yourself, and post back if there is anything specific you don't understand while doing so.
John
March 1, 2012 at 3:56 am
This is the script that i had written. From this i am able to get the schedule of the jobs. But what i need is - I need to get the schedule on the basis of date, i.e. for a given date, i need to to see whether the job is scheduled or not.. and if it is scheduled, whether it ran or not.. and if it ran, what was the time, status, message, duration, etc.
SELECT
[schedule_uid] AS [ScheduleID]
, [name] AS [ScheduleName]
, CASE [enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, CASE
WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
WHEN [freq_type] = 1 THEN 'One Time'
END [ScheduleType]
, CASE [freq_type]
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPUs become idle'
END [Occurrence]
, CASE [freq_type]
WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' week(s) on '
+ CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
+ ' of every '
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
WHEN 32 THEN 'Occurs on '
+ CASE [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 [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 ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' month(s)'
END AS [Recurrence]
, CASE [freq_subday_type]
WHEN 1 THEN 'Occurs once at '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN 'Occurs every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 4 THEN 'Occurs every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 8 THEN 'Occurs every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
END [Frequency]
, STUFF(
STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
, 8, 0, '-') AS [ScheduleUsageStartDate]
, STUFF(
STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
, 8, 0, '-') AS [ScheduleUsageEndDate]
, [date_created] AS [ScheduleCreatedOn]
, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]
March 1, 2012 at 4:23 am
I think a Dates table would come in very handy here. You can create one something like this:
WITH Numbers(N) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY c1.name)
FROM
master.sys.columns c1
CROSS JOIN
master.sys.columns c2
)
SELECT
DATEADD(d,N,'19000101')
INTO
Dates
FROM
Numbers
WHERE
N <= 73049 -- gives 200 years of dates
You can then join this to what you already have to find out which schedules will execute on which days. Join to sysjobs to relate jobs to schedules, and if you want to know the results as well, join to sysjobhistory.
John
March 2, 2012 at 7:48 pm
John Mitchell-245523 (3/1/2012)
I think a Dates table would come in very handy here. You can create one something like this:
WITH Numbers(N) AS (SELECT
ROW_NUMBER() OVER (ORDER BY c1.name)
FROM
master.sys.columns c1
CROSS JOIN
master.sys.columns c2
)
SELECT
DATEADD(d,N,'19000101') as 'Date'
INTO
Dates
FROM
Numbers
WHERE
N <= 73049 -- gives 200 years of dates
You can then join this to what you already have to find out which schedules will execute on which days. Join to sysjobs to relate jobs to schedules, and if you want to know the results as well, join to sysjobhistory.
John
added: as 'Date' to avoid error
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply