SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Pedro Lopes / Check Availability Window - SQL2k5 / Latest Posts

RE: Check Availability Window - SQL2k5

Lars Søe Mikkelsen — Tue, 22 Jan 2008 01:55:07 GMT

Thanks for the script. This will save some of our precious time :)

RE: Check Availability Window - SQL2k5

Pedro Lopes MS — Fri, 11 Jan 2008 08:05:57 GMT

Sorry all, i had noticed it and submited it together with the above changes, but forgot to mention them... Change all 'dba_database' with 'msdb' until the edited version is approved and published. :ermm:

RE: Check Availability Window - SQL2k5

Paul Sturm — Fri, 11 Jan 2008 06:05:27 GMT

I had to remove 'dba_database.' (3x) from the script after applying your changes to make it work.

RE: Check Availability Window - SQL2k5

Pedro Lopes MS — Fri, 11 Jan 2008 04:12:10 GMT

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) + 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) + 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())[b], 0)[/b], @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.

RE: Check Availability Window - SQL2k5

devvw — Fri, 11 Jan 2008 01:27:04 GMT

There is also a problem with the msdb.dbo.sysschedules table that doesn't exist

RE: Check Availability Window - SQL2k5

devvw — Fri, 11 Jan 2008 01:00:21 GMT

I get an "Incorrect syntax near '='." when executing the scriptIt looks like the problem is located in the @numdays=DATEDIFF.... part but can figure out the solutionSELECT @startdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) - 30, @enddate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), @numdays = DATEDIFF(DAY,@startdate,@enddate))

Check Availability Window - SQL2k5

Pedro Lopes MS — Tue, 30 Oct 2007 23:03:15 GMT

Comments posted to this topic are about the item [B]Check Availability Window - SQL2k5[/B]