July 19, 2012 at 7:13 pm
Comments posted to this topic are about the item SQL Agent Job schedule details
July 24, 2012 at 6:04 am
Hi Mitesh,
I am getting this following error.:(
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '?'.
July 25, 2012 at 12:02 am
If you copy and paste the script in Query Window, then there may be some formatting issue, if you paste the script in SSMS then you will see red line at the beginning of the line. Please remove any extract character that has come along as part of copy and paste.
July 27, 2012 at 12:56 pm
Nice. I use this:
SELECT jobs.name AS 'JobName',
sched.name AS 'SchedName',
CASE sched.freq_type
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly'
END AS 'Freq_type',
CASE sched.freq_subday_type
WHEN 1 THEN 'At specified time'
WHEN 2 THEN 'Seconds'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS 'Freq_subday_type',
sched.freq_subday_interval,
sched.active_start_time ,
sched.active_end_time
FROM msdb..sysjobs jobs
INNER JOIN msdb..sysjobschedules job_sched ON job_sched.job_id=jobs.job_id
INNER JOIN msdb..sysschedules sched ON sched.schedule_id=job_sched.schedule_id
WHERE jobs.enabled = 1 AND sched.enabled = 1 AND sched.freq_type NOT IN (1, 64, 128)
ORDER BY active_start_time
freq_subday_interval seems to have better info than freq_interval.
As for the formatting, this is a common issue here. I put your code block into UltraEdit where all those non-printable characters display as "?" and deleted them.
Ken
October 16, 2013 at 2:19 pm
First let me start by acknowledging I probably have WAY TOO MUCH TIME on my hands. LOL
That said, I made a small, :), change to the query around the 'next_run_time' result. It really bothered me that it was in ISO 'datetime' format and made it hard to use in a reporting system I'm creating, which has a non-technical audience.
I needed the 'timedate' results for all related columns to return in standard US 'datetime' format. As well as to include the time along with the date. I also changed the Boolean for the 'Enabled' column to read YES or NO.
Please don't misread my comments! This was entirely for my use and the original script is an OUTSTANDING script! I just needed to tweak it for my purposes. So I figured some others out there may benefit from my tweak.
So, here it is:
USE msdb
GO
SELECT DISTINCT
s.[name] AS [Job Name], s.[description] AS [Job Description],
dbo.SQLAGENT_SUSER_SNAME (s.owner_sid) AS [Job Owner],
(CASE WHEN s.[enabled] = 0 THEN 'NO' ELSE 'YES' END) AS [Enabled],
s.date_created AS [Date Created], s.date_modified AS [Date Modified],
(SUBSTRING(CAST(sjs.next_run_date AS VARCHAR(8)), 1, 4)) + '-' +
(SUBSTRING(CAST(sjs.next_run_date AS VARCHAR(8)), 5, 2)) + '-' +
(SUBSTRING(CAST(sjs.next_run_date AS VARCHAR(8)), 7, 2)) + ' ' +
(CONVERT(datetime,
SUBSTRING((CASEWHEN LEN(sjs.next_run_time) = 4 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00000'
WHEN LEN(sjs.next_run_time) = 5 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0000'
WHEN LEN(sjs.next_run_time) = 6 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '000'
WHEN LEN(sjs.next_run_time) = 7 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00'
WHEN LEN(sjs.next_run_time) = 8 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0'
ELSE '' END), 1, 2) + ':' +
SUBSTRING((CASEWHEN LEN(sjs.next_run_time) = 4 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00000'
WHEN LEN(sjs.next_run_time) = 5 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0000'
WHEN LEN(sjs.next_run_time) = 6 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '000'
WHEN LEN(sjs.next_run_time) = 7 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00'
WHEN LEN(sjs.next_run_time) = 8 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0'
ELSE '' END), 3, 2) + ':' +
SUBSTRING((CASEWHEN LEN(sjs.next_run_time) = 4 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00000'
WHEN LEN(sjs.next_run_time) = 5 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0000'
WHEN LEN(sjs.next_run_time) = 6 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '000'
WHEN LEN(sjs.next_run_time) = 7 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00'
WHEN LEN(sjs.next_run_time) = 8 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0'
ELSE '' END), 5, 2) + '.' +
SUBSTRING((CASEWHEN LEN(sjs.next_run_time) = 4 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00000'
WHEN LEN(sjs.next_run_time) = 5 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0000'
WHEN LEN(sjs.next_run_time) = 6 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '000'
WHEN LEN(sjs.next_run_time) = 7 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '00'
WHEN LEN(sjs.next_run_time) = 8 THEN CAST(sjs.next_run_time AS VARCHAR(9)) + '0'
ELSE '' END), 7, 3), 121)) AS [Next Run Time],
JobFrequency = (CASE freq_type WHEN 1 THEN 'One time only'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq_interval'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle' ELSE 'Unknown' END),
JobInterval = (CASE WHEN freq_type = 8 THEN
CASEWHEN freq_interval = 1 THEN 'Sunday'
WHEN freq_interval = 2 THEN 'Monday'
WHEN freq_interval = 4 THEN 'Tuesday'
WHEN freq_interval = 8 THEN 'Wednesday'
WHEN freq_interval = 16 THEN 'Thursday'
WHEN freq_interval = 32 THEN 'Friday'
WHEN freq_interval = 64 THEN 'Saturday' ELSE
CASE WHEN freq_type = 32 THEN
CASEWHEN freq_interval = 1 THEN 'Sunday'
WHEN freq_interval = 2 THEN 'Monday'
WHEN freq_interval = 3 THEN 'Tuesday'
WHEN freq_interval = 4 THEN 'Wednesday'
WHEN freq_interval = 5 THEN 'Thursday'
WHEN freq_interval = 6 THEN 'Friday'
WHEN freq_interval = 7 THEN 'Saturday'
WHEN freq_interval = 8 THEN 'Day'
WHEN freq_interval = 9 THEN 'Weekday'
WHEN freq_interval = 10 THEN 'Weekend day' ELSE
CASE WHEN freq_type = 1 THEN 'Once' ELSE
CASE WHEN freq_type = 4 THEN 'Everyday' ELSE 'Unknown'
END
END
END
END
END
END)
FROM dbo.sysjobs s
LEFT JOIN dbo.sysjobschedules sjs ON s.job_id = sjs.job_id
LEFT JOIN master.sys.server_principals sp ON s.owner_sid = sp.sid
LEFT JOIN sysschedules ss ON sjs.schedule_id = ss.schedule_id
WHERE s.description NOT LIKE 'This job is owned by a report server%'
Have fun with it and I'm curious to see what the next person does to this! Thanks for your efforts in this, it really helped me move my report forward!
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
May 10, 2016 at 9:23 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply