Check Availability Window - SQL2k5

  • Pedro Lopes MS

    SSChasing Mays

    Points: 612

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

  • devvw

    SSC Enthusiast

    Points: 123

    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: 123

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

  • Pedro Lopes MS

    SSChasing Mays

    Points: 612

    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.

  • Paul Sturm

    SSC Enthusiast

    Points: 114

    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

    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

    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