Check Availability Window - SQL2k5
-
Comments posted to this topic are about the item Check Availability Window - SQL2k5
-
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))
-
There is also a problem with the msdb.dbo.sysschedules table that doesn't exist
-
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.
-
I had to remove 'dba_database.' (3x) from the script after applying your changes to make it work.
-
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:
-
Thanks for the script. This will save some of our precious time
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply