Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check Availability Window - SQL2k5 Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2007 11:03 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 2012 9:04 AM
Points: 84, Visits: 144
Comments posted to this topic are about the item Check Availability Window - SQL2k5
Post #416870
Posted Friday, January 11, 2008 1:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:03 AM
Points: 7, Visits: 143
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
Posted Friday, January 11, 2008 1:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:03 AM
Points: 7, Visits: 143
There is also a problem with the msdb.dbo.sysschedules table that doesn't exist


Post #441603
Posted Friday, January 11, 2008 4:12 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 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
Posted Friday, January 11, 2008 6:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 6:40 AM
Points: 76, Visits: 879
I had to remove 'dba_database.' (3x) from the script after applying your changes to make it work.
Post #441684
Posted Friday, January 11, 2008 8:05 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 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.
Post #441784
Posted Tuesday, January 22, 2008 1:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:21 AM
Points: 70, Visits: 480
Thanks for the script. This will save some of our precious time :)
Post #445767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse