-- Stored Procedure: dbo.MonthlyMaintenanceWindowCheck
-- Bill WUnder
use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MonthlyMaintenanceWindowCheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MonthlyMaintenanceWindowCheck]
GO



CREATE PROCEDURE [dbo].[MonthlyMaintenanceWindowCheck] 
 @dt datetime = null
AS
/*******************************************************************************************************
* admin.dbo.MonthlyMaintenanceWindowCheck 
* Creator:       bw
* Date:          10-28-2003
*
* Description:   raise an error if in monthly maintenance window 
* Notes:         Window is from midnight to 4AM ET 
*         on the Sunday after the first Saturday of the month
*                
* Usage:
      EXECUTE admin.dbo.MonthlyMaintenanceWindowCheck 
      EXECUTE admin.dbo.MonthlyMaintenanceWindowCheck '11-02-2003 00:03:00'
      EXECUTE admin.dbo.MonthlyMaintenanceWindowCheck '11-02-2003 04:00:00'
      EXECUTE admin.dbo.MonthlyMaintenanceWindowCheck '11-08-2003 04:00:00'
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------
if @dt is null
 set @dt = current_timestamp

if @dt is null
 set @dt = getdate()

if Not(datepart(weekday,@dt-1) = 7  -- yesterday was saturday
 and datepart(day,@dt-1) < 8)  -- yesterday was in first week of month
  And datepart(hour, @dt) = 4  -- now 4AM
 raiserror( 'Request for delayed (4AM) Sunday start when not Sunday morning after monthly maintenance window aborted.',16,1)

if datepart(weekday,@dt-1) = 7   -- yesterday was saturday
  And datepart(day,@dt-1) < 8   -- yesterday was in first week of month
  And datepart(hour, @dt) < 4  -- not yet 4AM
 raiserror( 'Request to run during monthly maintenance window aborted.',16,1)


RETURN


GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO