Check Availability Window - SQL2k5

Question

Post reply

Check Availability Window - SQL2k5

Pedro Lopes MS

SSChasing Mays

Points: 612
More actions
October 30, 2007 at 11:03 pm

#176628

Comments posted to this topic are about the item Check Availability Window - SQL2k5

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

devvw SSC Enthusiast Points: 153 More actions · Answer 1

I get an "Incorrect syntax near '='." when executing the script

It looks like the problem is located in the @numdays=DATEDIFF.... part but can figure out the solution

SELECT @startdate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) - 30, @enddate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), @numdays = DATEDIFF(DAY,@startdate,@enddate))

devvw SSC Enthusiast Points: 153 More actions · Answer 2

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

Pedro Lopes MS SSChasing Mays Points: 612 More actions · Answer 3

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.

Paul Sturm SSC Enthusiast Points: 114 More actions · Answer 4

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

Pedro Lopes MS SSChasing Mays Points: 612 More actions · Answer 5

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:

Lars Søe Mikkelsen SSCommitted Points: 1602 More actions · Answer 6

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