Check Availability Window

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Pedro Lopes » Check Availability Window - SQL2k5

Check Availability Window - SQL2k5

Rate Topic

Display Mode

Topic Options

Author

Message

Pedro Lopes MS

Posted Tuesday, October 30, 2007 11:03 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 07, 2012 9:04 AM
Points: 84, Visits: 144

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

Post #416870

devvw

Posted Friday, January 11, 2008 1:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 06, 2013 6:47 AM
Points: 7, Visits: 127

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))

Post #441600

devvw

Posted Friday, January 11, 2008 1:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 06, 2013 6:47 AM
Points: 7, Visits: 127

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

Post #441603

Pedro Lopes MS

Posted Friday, January 11, 2008 4:12 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 07, 2012 9:04 AM
Points: 84, Visits: 144

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()), 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.

Post #441653

Paul Sturm

Posted Friday, January 11, 2008 6:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, June 07, 2013 9:27 AM
Points: 75, Visits: 873

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

Post #441684

Pedro Lopes MS

Posted Friday, January 11, 2008 8:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 07, 2012 9:04 AM
Points: 84, Visits: 144

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

Post #441784

Lars Søe Mikkelsen

Posted Tuesday, January 22, 2008 1:55 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 23, 2013 5:28 AM
Points: 68, Visits: 426

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

Post #445767

« Prev Topic | Next Topic »

Permissions