Looks like some of the indentation i made in the online editor when submiting broke the script. I resubmited it.
Look for:
SELECT @datestr = CONVERT(NVARCHAR(4), @year) + N''-'' +
CONVERT(NVARCHAR(2), @month) + N''-'' +
CONVERT(NVARCHAR(4), @day) + N'' '' +
REPLACE(CONVERT(NVARCHAR(2), @hour) + N'':'' +
CONVERT(NVARCHAR(2), @min-2) + N'':'' +
CONVERT(NVARCHAR(2), @sec), '' '', ''0'')
and change it to:
SELECT @datestr = CONVERT(NVARCHAR(4), @year) + N''-'' + CONVERT(NVARCHAR(2), @month) + N''-'' + CONVERT(NVARCHAR(4), @day) + N'' '' + REPLACE(CONVERT(NVARCHAR(2), @hour) + N'':'' + CONVERT(NVARCHAR(2), @min-2) + N'':'' + CONVERT(NVARCHAR(2), @sec), '' '', ''0'')
Also looks for:
SELECT @startdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) - 30, @enddate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), @numdays = DATEDIFF(DAY,@startdate,@enddate)
and change it to:
SELECT @startdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) - 30, @enddate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0), @numdays = DATEDIFF(DAY,@startdate,@enddate)
😉
About msdb.dbo.sysschedules, check BOL (Contains information about SQL Server Agent job schedules. This table is stored in the msdb database.), it's there - on SQL 2005, as i've told this script is only compatible with SQL 2k5.
Also available for 2000 here, with some diferences: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/61431/
I also resubmited it because of indentation mistakes.