woensdag 31 december 2008

Find user Permissions --sp_resolveperms (Great Script)

Extracting/Copying Users and Permissions Between Databases:
==========================================================
http://www.codeguru.com/cpp/data/mfc_database/storedprocedures/article.php/c12375__3/

----------------------------------------------------------
DECLARE @currentGroup SYSNAME
SET @currentGroup = 'BINCK\app_tekortentool'
EXEC master..xp_logininfo @currentGroup, @option='members'
------------------------------------------------------------

CREATE PROCEDURE sp_resolveperms AS

-- Programmer: Mark J. McGinty
-- Purpose: Resolve/list object-level permissions
-- Date: 19 Oct 2004

SELECT [object name], [user or role],
CASE MAX([select]) WHEN 205 THEN 'allowed' WHEN 206 THEN
'denied' ELSE '' END AS [select],
CASE MAX([insert]) WHEN 205 THEN 'allowed' WHEN 206 THEN
'denied' ELSE '' END AS [insert],
CASE MAX([update]) WHEN 205 THEN 'allowed' WHEN 206 THEN
'denied' ELSE '' END AS [update],
CASE MAX([delete]) WHEN 205 THEN 'allowed' WHEN 206 THEN
'denied' ELSE '' END AS [delete],
CASE MAX([execute]) WHEN 205 THEN 'allowed' WHEN 206 THEN
'denied' ELSE '' END AS [execute],
CASE MAX([DRI]) WHEN 205 THEN 'allowed' WHEN 206 THEN
'denied' ELSE '' END AS [DRI]
FROM (
SELECT TOP 100 PERCENT o.name AS [object name],
user_name(o.uid) AS [schema],
user_name(p.uid) AS [user or role],
o.sysstat & 0xf AS [sysstat],
p.id,
CASE action WHEN 193 THEN 'select'
WHEN 195 THEN 'insert'
WHEN 196 THEN 'update'
WHEN 197 THEN 'delete'
WHEN 224 THEN 'execute'
WHEN 26 THEN 'DRI'--References
END AS PrivilegeDesc,

CASE action WHEN 193 THEN protecttype ELSE 0 END AS [select],
CASE action WHEN 195 THEN protecttype ELSE 0 END AS [insert],
CASE action WHEN 196 THEN protecttype ELSE 0 END AS [update],
CASE action WHEN 197 THEN protecttype ELSE 0 END AS [delete],
CASE action WHEN 224 THEN protecttype ELSE 0 END AS [execute],
CASE action WHEN 26 THEN protecttype ELSE 0 END AS [DRI],
CASE protecttype WHEN 205 THEN 'allowed'
WHEN 206 THEN 'denied'
ELSE 'unknown' END AS AccessGrant,
action,
protecttype
FROM dbo.sysprotects p, dbo.sysobjects o
WHERE o.id = p.id
AND p.action IN (193, 195, 196, 197, 224, 26)
--AND p.id = 437576597
ORDER by 1, 2
) drs GROUP BY [object name], [user or role]

GO


exec sp_resolveperms

dinsdag 30 december 2008

Script to validate user input for password change

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30718/
==========================================================================
For SQL Server in Mixed Authentication mode this
stored procedure helps to validate users password. Currently this procedure checks for the next requrements: password must have length at least 8 characters plus among them at least one digit and at least one of the characters must be in upper case.
/***************************************************************************
Script to validate user input for password change

Based on original Microsoft SQl Server stored procedure sp_password
with modifications.

For SQL Server in Mixed Authentication mode this stored procedure
will help to control modifications of users passwords.

Currently procedure checks for next requrements:
password must have length at least 8 characters plus among them
at least one digit and at least one of the characters must be in upper case.

D.Bobkov
March 11, 2003

****************************************************************************/

use master

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

create procedure sp_password
@old sysname = NULL, -- the old (current) password
@new sysname, -- the new password
@loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @self int
select @self = CASE WHEN @loginame is null THEN 1 ELSE 0 END

-- CHECK PERMISSIONS --
IF (not is_srvrolemember('sysadmin') = 1)
AND not @self = 1
begin
raiserror(15210,-1,-1)
return (1)
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
return (1)
end

-- RESOLVE LOGIN NAME (disallows nt names)
if @loginame is null
select @loginame = suser_sname()
if not exists (select * from master.dbo.syslogins where
loginname = @loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end

-- CHECK OLD PASSWORD IF NEEDED --
if (@self = 1 or @old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @loginame and
( (@old is null and password is null) or
(pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
return (1)
end
--=========================================================================================
-- D.Bobkov - change for VALIDATE USER INPUT ===============================================
-- as example using: minimum length - 8 char, minimum 1 number and minimum 1 capital letter in it...
-- Perform comparision of @new
declare @NumPos int
declare @CapsPos int
declare @position int
declare @CharValue int

SET @position = 1
SET @NumPos = 0
SET @CapsPos = 0

WHILE @position <= DATALENGTH(@new)
BEGIN
SET @CharValue = ASCII(SUBSTRING(@new, @position, 1))
IF ( @CharValue > 47 AND @CharValue < 58)
SET @NumPos = @NumPos + 1
ELSE IF ( @CharValue > 64 AND @CharValue < 91)
SET @CapsPos = @CapsPos + 1
SET @position = @position + 1
END
IF DATALENGTH(CAST(@new AS varchar(20))) < 8
begin
raiserror('Password length is less than 8 chars', 16, 1)
return (1)
end

IF @NumPos < 1
begin
raiserror('Password must have at least one digit', 16, 1)
return (1)
end

IF @CapsPos < 1
begin
raiserror('Password must have at least one cahracter in upper case', 16, 1)
return (1)
end
-- END OF D.Bobkov change ===================================================================
--=========================================================================================
-- CHANGE THE PASSWORD --
update master.dbo.sysxlogins
set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
where name = @loginame and srvid IS NULL

-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @@error <> 0
return (1)
raiserror(15478,-1,-1)
return (0) -- sp_password

GO

SQL Server 2000 Security --Use OSQL

http://www.governmentsecurity.org/archive/t7317.html
===========================================================================
This file (lockdown.sql) was on one of my servers i read it and It says that secures a few threats of a clean install.

I hope it can help u guys secure your servers. And if some one could explain to me how to execute it that would be nice

--SQL Server 2000 Lockdown Script
--by Chip Andrews (www.sqlsecurity.com)
--12/23/2002
--
--The purpose of this script is to provide administrators (SQL Server or otherwise) a baseline
--lockdown configuration for new installations. These settings should disable potentially dangerous
--functionality while leaving the server operational and still capabable of Service Pack and hotfix
--installations. Feel free to provide feedback at www.sqlsecurity.com if you find any issues or
--have any suggestions for improvement.
--
--Project Goals:
-- * Must support named instances
-- * Must not break future Service Pack and hotfixes installations
-- * Must strive to disable rarely used functionality but not break common applications (80-20 rule)
-- * Must be easily runnable from the command prompt for mass distribution
--
--Notes:
--
--*You will note that no Extended Stored Procedures have been dropped in the script. This is due to several reasons:
-- 1. It causes some problems with Service Packs and hotfix installations when certain functions are disabled
-- 2. Blocking access to non-sysadmin users is more easily achieved by dropping execute permissions
-- 3. Sysadmins can easily add them back so dropping them really serves no real purpose
--*The last script item has been commented out because it effectively blocks all network access to the SQL Server
-- and thus violates the 80-20 rule. Feel free to enable it for local-only SQL Server installs.
--
--
SET NOCOUNT ON
PRINT '*** Begin SQL Server 2000 Lockdown Script v1.0 ***'
PRINT ''
PRINT 'SERVER NAME : ' + @@SERVERNAME
PRINT ''
--
--
--Check SQL Server Service Account for LocalSystem Authority - Send warning
--It should be noted that it may be possible to create a local account if LocalSystem is found
--and alter the service account here in the script. However, since there are also file ACL and registry
--permissions to deal with then its probably best left to the Enterprise Manager to do this.
CREATE TABLE #user (value VARCHAR(50), data VARCHAR(50))
IF (charindex('\',@@SERVERNAME)=0)
INSERT #user EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSSQLSERVER','ObjectName'
ELSE
BEGIN
PRINT 'Note: SQL Server was determined to be a named instance'
PRINT ''
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'SYSTEM\CurrentControlSet\Services\MSSQL$' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
INSERT #user EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@RegistryPath,'ObjectName'
END
SELECT TOP 1 DATA AS [SQL Server Service Account] FROM #USER
IF (SELECT TOP 1 DATA FROM #user)='LocalSystem'
PRINT '*** ALERT LOCALSYSTEM AUTHORITY BEING USED FOR SQL SERVER SERVICE ACCOUNT IS NOT RECOMMENDED. ***'
DROP TABLE #user
PRINT ''
--
-- Confirm the latest service pack and hotfixes have been applied by selecting
-- the server version and comparing it to the most current SQL Server
-- version (at the time of writing that was 8.00.665 for SQL Server 2000).
-- (Although we are not applying the latest patch in this script, we can still
-- output a message warning the user of the script to apply the needed patches as long as you capture the output.)
SELECT @@version as [SQL Server Version]
IF NOT (charindex('8.00.665',@@version)>0)
BEGIN
print '*** WARNING - SQL Server NOT PROPERLY PATCHED! ***'
END
GO
--
-- Enable Windows Authentication as the only login method to prevent against 'sa'
-- account attacks and the weak internal SQL Server authentication model.
IF (charindex('\',@@SERVERNAME)=0)
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'LoginMode',N'REG_DWORD',1
ELSE
BEGIN
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'Software\Microsoft\Microsoft SQL Server\' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME)) + '\MSSQLServer'
EXECUTE master..xp_regwrite 'HKEY_LOCAL_MACHINE',@RegistryPath,N'LoginMode',N'REG_DWORD',1
END
GO
--
-- Set strong 'sa' account password (in this case a concatenation of two
-- unique identifiers). This password can easily be reset later by using a
-- trusted connection while logged in as a local administrator or any user
-- who is a member of the System Administrator role.
DECLARE @pass char(72)
SELECT @pass=convert(char(36),newid())+convert(char(36),newid())
EXECUTE master..sp_password null,@pass,'sa'
GO
--
-- Enable full auditing to monitor both successful and failed access to the
-- SQL Server. You may want to scale this back to failed-only is log space
-- is a problem.
IF (charindex('\',@@SERVERNAME)=0)
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel',N'REG_DWORD',3
ELSE
BEGIN
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'Software\Microsoft\Microsoft SQL Server\' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME)) + '\MSSQLServer'
EXECUTE master..xp_regwrite 'HKEY_LOCAL_MACHINE',@RegistryPath,N'AuditLevel',N'REG_DWORD',3
END
GO
--
-- Disable SQLAgent, Microsoft Distributed Transaction Coordinator (MSDTC), and MSSEARCH
-- since they may potentially represent unnecessary services. There are no multiple instances of these services.
EXECUTE msdb..sp_set_sqlagent_properties @auto_start = 0
GO
EXECUTE master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSDTC', N'Start', N'REG_DWORD', 3
GO
EXECUTE master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSEARCH', N'Start', N'REG_DWORD', 3
GO
--
--Diable adhoc queries for each data provider since this functionality is ripe for abuse. Once again, if
--your application requires this you can add the functionality back on a per provider basis.
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\SQLOLEDB',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\Microsoft.Jet.Oledb.4.0',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSDAORA',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\ADSDSOObject',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\DB2OLEDB',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSIDXS',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSQLImpProv',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSSEARCHSQL',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSDASQL',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
--
--Remove the pubs and northwind sample databases since they represent known targets with minimal
--permissions for potential attackers.
USE master
DROP DATABASE northwind
DROP DATABASE pubs
GO
--
--Tighten permissions on jobs procedures in case the SQL Agent service is ever activated to prevent low
--privilege users from submitting or managing jobs.
USE msdb
REVOKE execute on sp_add_job to public
REVOKE execute on sp_add_jobstep to public
REVOKE execute on sp_add_jobserver to public
REVOKE execute on sp_start_job to public
GO
--
--Tighten permissions on web tasks table to keep malicious users from creating or altering tasks.
USE msdb
REVOKE update on mswebtasks to public
REVOKE insert on mswebtasks to public
GO
--
--Tighten permissions on DTS package connection table so that malicious users cannot affect DTS packages.
USE msdb
REVOKE select on RTblDBMProps to public
REVOKE update on RTblDBMProps to public
REVOKE insert on RTblDBMProps to public
REVOKE delete on RTblDBMProps to public
GO
--
--Tighten permissions on extended procedures that require heavy use but should not be allowed public access.
USE master
REVOKE execute on sp_runwebtask to public
REVOKE execute on sp_readwebtask to public
REVOKE execute on sp_MSSetServerProperties to public
REVOKE execute on sp_MScopyscriptfile to public
REVOKE execute on sp_MSsetalertinfo to public
REVOKE execute on xp_regread to public
REVOKE execute on xp_instance_regread to public
GO
--
--Revoke guest access to msdb in order to keep any non system administrators from accessing the database without explicit permissions.
USE msdb
EXECUTE sp_revokedbaccess guest
GO
--
--Turn off allow remote access to keep other SQL Servers from connecting to this server via RPC.
EXECUTE sp_configure 'remote access', '0'
GO
RECONFIGURE WITH OVERRIDE
GO
--
--Verify that the capability to allow access to system tables is disabled.
EXECUTE sp_configure 'allow updates', '0'
GO
RECONFIGURE WITH OVERRIDE
GO
--
--Increase SQL Server log history threshold in order to maintain logs for a longer amount of time.
IF (charindex('\',@@SERVERNAME)=0)
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'NumErrorLogs',N'REG_DWORD',365
ELSE
BEGIN
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'Software\Microsoft\Microsoft SQL Server\' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME)) + '\MSSQLServer'
EXECUTE master..xp_regwrite 'HKEY_LOCAL_MACHINE',@RegistryPath,N'NumErrorLogs',N'REG_DWORD',365
END
GO
--
--Remove any residual setup files (\sqldir\setup.iss - \winnt\setup.iss - \winnt\sqlstp.log) that may be
--lingering on the file system. These scripts now include system variables thanks to Carlos Moran
EXECUTE master.dbo.xp_cmdshell 'if exist %windir%\setup.iss del %Windir%\setup.iss'
GO
EXECUTE master.dbo.xp_cmdshell 'if exist %windir%\sqlstp.log del %Windir%\sqlstp.log'
GO
EXECUTE master.dbo.xp_cmdshell 'if exist "%ProgramFiles%\microsoft sql server\mssql\install\setup.iss" del "%ProgramFiles%\microsoft sql server\mssql\install\setup.iss"'
GO
--
--Remove any un-used network libraries. Since this is a hardened server, all netlibs can be removed until
--external connectivity requirements are identified. Connections to the local server are still possible
--using the Shared Memeory netlib which is always in effect by specifiying '(local)' or (a period) as
--the server name. The other netlibs can easily be restored using the Server Network Utility.
--(You must stop and restart the SQL Server for this change to go into effect)
--This step effectively blocks all network access to this SQL Server. Please be advised of this before
--applying this step.
--NOTE: This step has been removed by default. Feel free to add it when applying to local-only installations
--such as MSDE applications or Visual Studio.NET/.NET SDK installs
--
--EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib', N'ProtocolList',N'REG_SZ',''
--GO
--
--
PRINT '*** Please Stop and Start the SQL Server Service Instance in order to implement changes ***'
PRINT ''
PRINT '*** End SQL Server 2000 Lockdown Script ***'

------------------------------------------------------
C:\>osql.exe -i lockdown.sql

Red Gate Script Example

-- Alter Database Recovery Models to Bulk_Logged

alter database Warehouse set recovery BULK_LOGGED
go
select databasepropertyex('Warehouse','recovery')
go


-- warehouse 2.4.5.0

SET NUMERIC_ROUNDABORT OFF
go
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
go
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
go
CREATE TABLE #tmpErrors (Error int)
go
SET XACT_ABORT ON
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
go

print 'Drop cronstraint DAF_DEELTREGELS_NOTA_FK on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS drop CONSTRAINT DAF_DEELTREGELS_NOTA_FK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint DAF_NOTA_REL_FK on DAF_NOTA'
go
alter table DAF_NOTA drop CONSTRAINT DAF_NOTA_REL_FK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint DAF_REL_PK on DAF_REL'
go
alter table DAF_REL drop CONSTRAINT DAF_REL_PK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint PK_DAF_DEELTREGELS on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS drop CONSTRAINT PK_DAF_DEELTREGELS
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Drop cronstraint DAF_NOTA_PK on DAF_NOTA'
go
alter table DAF_NOTA drop CONSTRAINT DAF_NOTA_PK
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_REL_PK to DAF_REL'
go
alter table DAF_REL add CONSTRAINT DAF_REL_PK PRIMARY KEY CLUSTERED (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR)
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_NOTA_PK on DAF_NOTA'
go
alter table DAF_NOTA add CONSTRAINT DAF_NOTA_PK PRIMARY KEY CLUSTERED (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR, DAF_NOTA_ID)
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_NOTA_REL_FK on DAF_NOTA'
go
alter table DAF_NOTA add CONSTRAINT DAF_NOTA_REL_FK FOREIGN KEY (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR) references DAF_REL
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_DEELTREGELS_PK on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS add CONSTRAINT DAF_DEELTREGELS_PK PRIMARY KEY CLUSTERED (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR, DAF_NOTA_ID, DAF_DEELTREGELS_ID)
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go

print 'Adding cronstraint DAF_DEELTREGELS_NOTA_FK on DAF_DEELTREGELS'
go
alter table DAF_DEELTREGELS add CONSTRAINT DAF_DEELTREGELS_NOTA_FK FOREIGN KEY (DAF_REL_REKNR, DAF_REL_REKSRT, DAF_REL_REKVAL, DAF_REL_DAGAFNR, DAF_NOTA_ID) references DAF_NOTA
go

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
go
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
go
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
go
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
go
DROP TABLE #tmpErrors
go

-- Alter Database Recovery Models to Full
alter database Warehouse set recovery FULL
go
select databasepropertyex('Warehouse','recovery')
go

Find conflicting SQL job schedules -- Best Script

http://education.sqlfarms.com/education/ShowPost.aspx?PostID=1225
=========================================================================

The following script is useful to job scheduling conflicts: If a job has more than one schedule, then it is possible that to have conlicting schedules, in which case the job may be invoked multiple times in a span of a few seconds. Schedule conflicts are found by finding all the run-dates and run-times for all jobs (or one job if requested by the user) and then finding whether two schedules run in less than X amount of seconds from one another.

Example: If a job has two schedules, one is monthly and another is weekly, and some dates and times coincide, then there would be conflicts in the job schedules.

Notes: The script is version-aware and works on SQL Server 2000 and 2005.
The script only examines enabled jobs and enabled schedules.

The actions taken by the script are as follows: The script accepts a start-time (@StartDateTimeToCalculate) and an end-time (@EndDateTimeToCalculate), which should be specified by the user. By default, the script analyzes all jobs on the server. If you wish to only analyze a single job, then specify the job_id for the desired job in the variable @JobID.

Step 1 - The script captures a snapshot of all needed SQL job information, including the job schedules, first step to run, active start & end time, creation date, and so on - everything that is needed to determine the dates and times when the jobs should have been run.

Step 2 - Reconstruct and revese engineer all the dates in which all jobs (or only the job in @JobID) are scheduled to run, between the dates @StartDateTimeToCalculate and @EndDateTimeToCalculate (note - if the script finds that these dates are two far apart,
it will normalize the time window and only consider relevant times). This is done in several steps:

Step 2.1 - jobs that are scheduled to run once
Step 2.2 - daily jobs
Step 2.3 - monthly (fixed) jobs
Step 2.4 - monthly (relative) jobs.
For more information about fixed and relative monthly
jobs, please see the SQL Server Books Online
(look up sysjobschedules in SQL 2000 or sysschedules in 2005).

The reconstructed run-dates found in Step 2 are stored in a temp table called #JobScheduledDates.

Step 3 - Find the run-times (times in which jobs are scheduled to run) for all the run-dates found in Step 2. The dates and times in which the SQL jobs are scheduled to run are stored in the temp table called #JobScheduledDatesAndTimes.

Step 4 - Find all scheduling conflicts by comparing the dates and times found in Step 3. If any two schedules fire less than X amount of seconds from one another (and X is configurable as the variable @TimeGapInSeconds), then all such encounters are considered
as schedule conflicts and will be reported back to the user.

User parameters:
@StartDateTimeToCalculate - start time to examine
@EndDateTimeToCalculate - end time to examine
@JobID - if users wish to analyze one job, then
set this variable to the wanted job_id value.
To get info for all server jobs - leave @JobID NULL.
@TimeGapInSeconds - see explanation for Step 4 above.

Note: The script is optimized for readability. Not for speed. To use the script effectively, the user should specify a wide enough time range, in which case it may takes several minutes for the script to complete.
-------------------------------------------
/* ============================================================================
Copyright: SQL Farms, Inc., www.sqlfarms.com. All rights reserved.
This code can be used only for non-redistributable purposes.
The code can be used for free as long as this copyright notice is not removed.
===============================================================================

The following script is useful to job scheduling conflicts:
If a job has more than one schedule, then it is possible that
to have conlicting schedules, in which case the job may
be invoked multiple times in a span of a few seconds.
Schedule conflicts are found by finding all the run-dates
and run-times for all jobs (or one job if requested by the
user) and then finding whether two schedules run in less
than X amount of seconds from one another.

Example: If a job has two schedules, one is monthly and another
is weekly, and some dates and times coincide, then there would
be conflicts in the job schedules.

Notes: The script is version-aware and works on SQL Server 2000 and 2005.
The script only examines enabled jobs and enabled schedules.

The actions taken by the script are as follows:
The script accepts a start-time (@StartDateTimeToCalculate)
and an end-time (@EndDateTimeToCalculate), which should be specified
by the user. By default, the script analyzes all jobs on the server.
If you wish to only analyze a single job, then specify the job_id
for the desired job in the variable @JobID.

Step 1 - The script captures a snapshot of all needed SQL job
information, including the job schedules, first step to run,
active start & end time, creation date, and so on - everything
that is needed to determine the dates and times when the jobs
should have been run.

Step 2 - Reconstruct and revese engineer all the dates in which
all jobs (or only the job in @JobID) are scheduled to run, between
the dates @StartDateTimeToCalculate and @EndDateTimeToCalculate
(note - if the script finds that these dates are two far apart,
it will normalize the time window and only consider relevant times).
This is done in several steps:

Step 2.1 - jobs that are scheduled to run once
Step 2.2 - daily jobs
Step 2.3 - monthly (fixed) jobs
Step 2.4 - monthly (relative) jobs.
For more information about fixed and relative monthly
jobs, please see the SQL Server Books Online
(look up sysjobschedules in SQL 2000 or sysschedules in 2005).

The reconstructed run-dates found in Step 2 are stored in a temp
table called #JobScheduledDates.

Step 3 - Find the run-times (times in which jobs are scheduled
to run) for all the run-dates found in Step 2. The dates and times
in which the SQL jobs are scheduled to run are stored in the temp
table called #JobScheduledDatesAndTimes.

Step 4 - Find all scheduling conflicts by comparing the
dates and times found in Step 3.
If any two schedules fire less than X amount of seconds from
one another (and X is configurable as the variable
@TimeGapInSeconds), then all such encounters are considered
as schedule conflicts and will be reported back to the user.

User parameters:
@StartDateTimeToCalculate - start time to examine
@EndDateTimeToCalculate - end time to examine
@JobID - if users wish to analyze one job, then
set this variable to the wanted job_id value.
To get info for all server jobs - leave @JobID NULL.
@TimeGapInSeconds - see explanation for Step 4 above.

Note: The script is optimized for readability. Not for speed.
To use the script effectively, the user should specify a wide
enough time range, in which case it may takes several minutes
for the script to complete.


Date Developer Notes Version
========= =============== ================================= ===========
06/15/2007 Omri Bahat Initial release 1.00
==================================================================================
Copyright: SQL Farms, Inc., www.sqlfarms.com. All rights reserved.
This code can be used only for non-redistributable purposes.
The code can be used for free as long as this copyright notice is not removed.
==================================================================================*/

DECLARE @StartDateTimeToCalculate DATETIME
DECLARE @EndDateTimeToCalculate DATETIME
DECLARE @TimeGapInSeconds INT
DECLARE @JobID NVARCHAR(64)

-- Time window to find conflicting schedules.
SET @StartDateTimeToCalculate = '10/1/2007'
SET @EndDateTimeToCalculate = '12/1/2007'

-- If two schedules have a gap of X seconds between 2 runs,
-- then the schedules are considered as conflicting.
-- X is stored as the variable @TimeGapInSeconds.
SET @TimeGapInSeconds = 30

-- To look at a specific job, specify the job id below.
-- To look at all jobs- leave it NULL.
SET @JobID = NULL -- valid values are NULL or job id, e.g., 'E37DD871-F0B7-44B9-9646-37144E03DC14'


-- Validate start and end times
IF @StartDateTimeToCalculate > @EndDateTimeToCalculate
BEGIN
RAISERROR('The start and end dates entered are not valid. Aborting.', 16, 1)
RETURN
END


-- Validate that the request job exists (if @JobID or @JobName are non-null)
IF @JobID IS NOT NULL AND NOT EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE job_id = @JobID)
BEGIN
RAISERROR('The requested job id does not exist on the server.', 16, 1)
RETURN
END

SET NOCOUNT ON

/* ========================================================
Step 1 - Take a snapshot of all jobs and schedules.

We start by recording the info for all jobs and schedules.
This info is first taken from the msdb database. Some fields
in msdb may not be accurate, so we also override the msdb
info (if needed) with information from the SQL Agent itself.

There are 2 goals here:
1. Take a snapshot of all jobs so if any new jobs and schedules
are changed on the server when the script runs, they will
not be considered.
2. Calculate a next_run_date and next_run_time for each job.
We only need this info to identify a date & time value
where the job was run or was scheduled to run. This
will help us to reverse-engineer the schedule of the job
between @StartDateTimeToCalculate and @EndDateTimeToCalculate.
This info is not used for any other purposes.
====================================================== */

DECLARE @sSPID VARCHAR(32)
DECLARE @SQLStr NVARCHAR(4000)

-- Get a snapshot of all enabled jobs and their associated schedule-parameters.
-- Note: The next run date stored in SQL Server (sysjobschedules) may not be accurate.
-- We begin by taking the values of next_run_date and next_run_time from sysjobschedules
-- and then override this value with the information return from the SQL Server Agent, if available.
IF OBJECT_ID('tempdb..#JobScheduleInfo') IS NOT NULL
DROP TABLE #JobScheduleInfo

CREATE TABLE #JobScheduleInfo (
job_id VARCHAR(64),
job_name NVARCHAR(128) COLLATE DATABASE_DEFAULT,
job_owner NVARCHAR(128) COLLATE DATABASE_DEFAULT,
schedule_id INT,
schedule_name NVARCHAR(128) COLLATE DATABASE_DEFAULT,
freq_type INT,
freq_interval INT,
freq_subday_type INT,
freq_subday_interval INT,
freq_relative_interval INT,
freq_recurrence_factor INT,
active_start_date INT,
active_end_date INT,
active_start_time INT,
active_end_time INT,
date_created DATETIME,
min_datetime_to_consider DATETIME, -- Indicates the min datetime to consider when returning results.
max_datetime_to_consider DATETIME, -- Indicates the max datetime to consider when returning results.
start_step_id INT,
next_run_date INT, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
next_run_time INT) -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.


EXEC('CREATE UNIQUE CLUSTERED INDEX CI_#JobScheduleInfo_' + @sSPID + '
ON #JobScheduleInfo (job_id, schedule_name) ')


-- The code to get the job and schedule info is different
-- between SQL 2K and 2K5.
IF CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 2) AS DECIMAL) < 9
SET @SQLStr = @SQLStr + '
INSERT INTO #JobScheduleInfo
SELECT CAST(a.job_id AS VARCHAR(64)),
a.name,
SUSER_SNAME(a.owner_sid),
b.schedule_id,
b.name,
b.freq_type,
b.freq_interval,
b.freq_subday_type,
b.freq_subday_interval,
b.freq_relative_interval,
b.freq_recurrence_factor,
b.active_start_date,
b.active_end_date,
b.active_start_time,
b.active_end_time,
a.date_created,
-- Initial value (updated later)
DATEADD(ss,
CAST(active_start_time/10000 AS INT)*3600 + (CAST(active_start_time/100 AS INT)%100)*60 + (active_start_time % 100),
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME)
),
-- Initial value (updated later)
DATEADD(ss,
CAST(active_end_time/10000 AS INT)*3600 + (CAST(active_end_time/100 AS INT)%100)*60 + (active_end_time % 100),
CAST(CAST(active_end_date AS VARCHAR(8)) AS DATETIME)
),
a.start_step_id,
b.next_run_date, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
b.next_run_time -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobschedules b
ON a.job_id = b.job_id
AND a.enabled = 1
AND b.enabled = 1 '
ELSE
SET @SQLStr = @SQLStr + '
INSERT INTO #JobScheduleInfo
SELECT CAST(a.job_id AS VARCHAR(64)),
a.name,
SUSER_SNAME(a.owner_sid),
b.schedule_id,
b.name,
b.freq_type,
b.freq_interval,
b.freq_subday_type,
b.freq_subday_interval,
b.freq_relative_interval,
b.freq_recurrence_factor,
b.active_start_date,
b.active_end_date,
b.active_start_time,
b.active_end_time,
a.date_created,
-- Initial value (updated later)
DATEADD(ss,
CAST(active_start_time/10000 AS INT)*3600 + (CAST(active_start_time/100 AS INT)%100)*60 + (active_start_time % 100),
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME)
),
-- Initial value (updated later)
DATEADD(ss,
CAST(active_end_time/10000 AS INT)*3600 + (CAST(active_end_time/100 AS INT)%100)*60 + (active_end_time % 100),
CAST(CAST(active_end_date AS VARCHAR(8)) AS DATETIME)
),
a.start_step_id,
z.next_run_date, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
z.next_run_time -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobschedules z
ON a.job_id = z.job_id
INNER JOIN msdb.dbo.sysschedules b
ON z.schedule_id = b.schedule_id
AND a.enabled = 1
AND b.enabled = 1'

IF @JobID IS NOT NULL
BEGIN
SET @SQLStr = @SQLStr + CHAR(10) + CHAR(13) + ' WHERE a.job_id = ' + @JobID
SET @SQLStr = 'IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE job_id = ' + @JobID
END

EXEC(@SQLStr)


-- Update min_datetime_to_consider - update occurs if date_created is greater than the active_start_date + time
-- or when @StartDateTimeToCalculate is greater than both of these values.
UPDATE #JobScheduleInfo
SET min_datetime_to_consider = CASE
WHEN date_created < @StartDateTimeToCalculate THEN @StartDateTimeToCalculate
ELSE date_created
END
WHERE -- Only update if the min_datetime_to_consider is smaller than the other dates.
min_datetime_to_consider < CASE
WHEN date_created < @StartDateTimeToCalculate THEN @StartDateTimeToCalculate
ELSE date_created
END

-- Similarly, update the max_date_to_consider
UPDATE #JobScheduleInfo
SET max_datetime_to_consider = @EndDateTimeToCalculate
WHERE max_datetime_to_consider > @EndDateTimeToCalculate -- Only update if the max_datetime_to_consider is greater than the other date.

-- Check for bogus values- when max_datetime_to_consider <= min_datetime_to_consider
UPDATE #JobScheduleInfo
SET max_datetime_to_consider = min_datetime_to_consider
WHERE max_datetime_to_consider <= min_datetime_to_consider


-- In order to get an accurate next_run_date or next_run_time,
-- we refer to the SQL agent and get whatever information is available there.
-- This is done by calling the (undocumented) extended stored proc
-- xp_sqlagent_enum_jobs. Note that xp_sqlagent_enum_jobs will only give
-- us the next run date & time for one schedule for the job.
-- In spite of this fact, we still call it because it may contain better
-- info than sysjobschedules, even if it only for a few jobs and schedules.

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results

CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)



-- Prerequisites: In SQL 2K5 and above, the 'Agent XPs' server configuration
-- option must be set to true. The script assumes that it is true.
-- See sp_configure for additional information.

DECLARE @CurrJobOwner NVARCHAR(128)

SELECT @CurrJobOwner = MIN(job_owner)
FROM #JobScheduleInfo

WHILE @CurrJobOwner IS NOT NULL
BEGIN
INSERT INTO #xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs 1, @CurrJobOwner

SELECT @CurrJobOwner = MIN(job_owner)
FROM #JobScheduleInfo
WHERE job_owner > @CurrJobOwner
END


-- Update #JobScheduleInfo with the information from #xp_results
-- for the relevant jobs and schedules. We do not update records
-- where the next_run_date is null or zero in #xp_results.
-- Those entries mean that the SQL Agent has not yet calculated
-- the next run date & time.

UPDATE a
SET next_run_date = b.next_run_date,
next_run_time = b.next_run_time
FROM #JobScheduleInfo a
INNER JOIN #xp_results b
ON a.job_id = b.job_id
AND a.schedule_id = b.next_run_schedule_id
AND b.next_run_date IS NOT NULL
AND b.next_run_time IS NOT NULL
AND b.next_run_date > 0



/* ========================================================================
Step 2 - Find all dates between @StartDateTimeToCalculate
and @EndDateTimeToCalculate in which the job is
scheduled to run.

For each job and every schedule in #JobScheduleInfo, we now get the dates
and the first time in each date in which the job is scheduled to run.
We create the temp table #JobScheduledDates for this purpose, which includes
the following fields:

RunDate - dates to run (datetime)
StartTimeEx (normalized format for active_start_time)
StartTimeInSecFromStartOfDay (# of seconds from the
beginning of the day when the job runs for the first time
in that day)
=========================================================================== */

IF OBJECT_ID('tempdb..#JobScheduledDates') IS NOT NULL
DROP TABLE #JobScheduledDates

CREATE TABLE #JobScheduledDates (
job_id VARCHAR(64),
schedule_name NVARCHAR(128),
RunDate DATETIME,
StartTimeEx CHAR(6),
StartTimeInSecFromStartOfDay INT)

EXEC('CREATE UNIQUE CLUSTERED INDEX CI_#JobScheduledDates_' + @sSPID + '
ON #JobScheduledDates (job_id, schedule_name, RunDate) ')

/* =========================================================
Step 2.1 - jobs that run once - freq_type = 1

freq_type = 1 for jobs that are scheduled to run once,
so calculating the RunDate and StartTime is not difficult
and can be done for all server jobs in one statement.
-- ======================================================= */

INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT job_id,
schedule_name,
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(active_start_time AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(active_start_time AS VARCHAR(6))))
FROM #JobScheduleInfo
WHERE freq_type = 1
AND CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME) BETWEEN @StartDateTimeToCalculate AND @EndDateTimeToCalculate


/* ==========================================================================
Step 2.2 - daily jobs - freq_type = 4 (freq_subday_type = 1, 4, 8)

This case is when a job runs every X days (freq_interval = X).
Calculating the dates for these jobs is not straightforward.
In order to figure out when the job was scheduled to run in the past
or will run in the future, we need to look at the next_run_date.
The reason is- if I schedule a job to run (say) every 5 days,
the first date in which the job will run is non-deterministic
and depends on the job creation date, the computer clock, and other
parameters. In other words, we look at next_run_date to latch
onto a deterministic date that is associated with the job.
This is done in a loop for each job and schedule.

Note: We only need to calculate scheduled run dates that fall between
MAX(active_start_date, @StartDateTimeToCalculate)
and MIN(active_end_date + 1, @EndDateTimeToCalculate).
========================================================================== */

DECLARE @CurrJobID UNIQUEIDENTIFIER
DECLARE @CurrScheduleName NVARCHAR(128)
DECLARE @CurrNextRunDate DATETIME
DECLARE @CurrActiveStartTime INT
DECLARE @CurrRunsEveryXDays INT -- this variable holds X.
DECLARE @i INT
DECLARE @CurrMinDate DATETIME
DECLARE @CurrMaxDate DATETIME


-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.


-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 4
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 4
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrRunsEveryXDays = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName

-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXDays until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

SET @i = 0

-- First, get all dates between next_run_date up to @CurrMaxDate
WHILE DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that we need to record this date
IF DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that it has not yet been recorded.
IF NOT EXISTS(SELECT * FROM #JobScheduledDates WHERE job_id = @CurrJobID AND schedule_name = @CurrScheduleName AND RunDate = DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) )
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

SET @i = 0

-- Now, get all the dates between @CurrMinDate and next_run_date
WHILE DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that we need to record this date
IF DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that it has not yet been recorded.
IF NOT EXISTS(SELECT * FROM #JobScheduledDates WHERE job_id = @CurrJobID AND schedule_name = @CurrScheduleName AND RunDate = DATEADD(dd, @i*@CurrRunsEveryXDays, @CurrNextRunDate) )
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd, -@i*@CurrRunsEveryXDays, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

-- Move on to the next daily schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 4
AND schedule_name > @CurrScheduleName
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END

SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 4
AND job_id > @CurrJobID
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0
END




/* ==========================================================================
Step 2.2 - weekly jobs - freq_type = 8 (freq_subday_type = 1, 4, 8)

This case is when a job runs every X weeks in specific days in each week.
Calculating the dates for these jobs is not straightforward: Say that a
job runs every 3 weeks on Sunday and Friday, and that the job was created
on Thursday. Will the next run be on the next Sunday, or since the job
runs on the following Friday- will it not run on the next Sunday?

The following logic is used to get the schedule for weekly jobs:
Check the next run date for the job. That week is when the job is run.
Then, look at every day of that week (we can ignore DATEFIRST since
it doesn't apply here) and conclude all days in that week in which
the job should run (this info is captured by freq_interval).
This allows us to latch on to deterministic dates for the job schedule.
Then, we calculate dates before and after that date in which the job should run.
========================================================================== */

DECLARE @CurrRunEveryXWeeks INT -- This variable captures X.
DECLARE @CurrDayVector INT -- This variable captures the day codes when the job runs
-- (1-Sun, 2-Mon, ..., 7-Sat), as taken from freq_interval.
DECLARE @DayCodeForNextRunDate INT
DECLARE @tmpMinDate DATETIME
DECLARE @tmpMaxDate DATETIME


-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.

-- First, we create a temp table that holds the week days.
-- This will help us to find the scheduled dates for weekly jobs later.
IF OBJECT_ID('tempdb..#DaysAndDates') IS NOT NULL
DROP TABLE #DaysAndDates

CREATE TABLE #DaysAndDates (
DayCode INT PRIMARY KEY CLUSTERED,
DayDesc VARCHAR(16), -- Has no functional use. For readability only.
TempFlag BIT, -- If this value is 1 for a given job, then the job runs in scheduled to run that day.
TempDate DATETIME) -- If this value is non-null for a given job, then the job runs in this scheduled date.

SET @i = 1

WHILE @i <= 7
BEGIN
INSERT INTO #DaysAndDates (DayCode, DayDesc, TempFlag)
VALUES(@i, CASE
WHEN @i = 1 THEN 'Sunday'
WHEN @i = 2 THEN 'Monday'
WHEN @i = 3 THEN 'Tuesday'
WHEN @i = 4 THEN 'Wednesday'
WHEN @i = 5 THEN 'Thursday'
WHEN @i = 6 THEN 'Friday'
WHEN @i = 7 THEN 'Saturday' END,
0)

SET @i = @i + 1
END

-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 8
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 8
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayVector = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXWeeks = freq_recurrence_factor,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName

-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXWeeks
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

-- Get the days in which the job runs - Reinitialize #DaysAndDates
-- and then set TempFlag = 1 in #DaysAndDates for each run day.
UPDATE #DaysAndDates
SET TempFlag = 0,
TempDate = NULL

-- The POWER function here translates between the day codes (1-7)
-- to the day vector (1, 2, 4, ..., 64) in freq_interval.
UPDATE #DaysAndDates
SET TempFlag = 1
WHERE @CurrDayVector & POWER(2, DayCode - 1) > 0

-- Find the code of the day that corresponds to next_run_date
SET @DayCodeForNextRunDate = 1 + (DATEDIFF (dd, CONVERT(DATETIME, '1899/12/31', 120), @CurrNextRunDate) % 7 )

-- Mark next_run_date as the TempDate for the appropriate day in #DaysAndDates.
-- Here we also update the TempDates for all days in which the job runs.
UPDATE #DaysAndDates
SET TempDate = DATEADD(dd, DayCode - @DayCodeForNextRunDate, @CurrNextRunDate)
WHERE TempFlag = 1

SELECT @tmpMinDate = MIN(TempDate),
@tmpMaxDate = MAX(TempDate)
FROM #DaysAndDates
WHERE TempFlag = 1


-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime
SET @i = 0

-- First, get all dates between the initial dates in #DaysAndDates up to @CurrMaxDate
WHILE DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, @tmpMinDate) <= @CurrMaxDate
BEGIN
-- Check that the max date in #JobScheduledDates is greater that the @CurrMinDate.
-- If it is not- none of the values in that table are in the time window
-- so there's nothing to write.
IF DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, @tmpMaxDate) >= @CurrMinDate
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
FROM #DaysAndDates a
LEFT OUTER JOIN #JobScheduledDates b
ON b.job_id = @CurrJobID
AND b.schedule_name = @CurrScheduleName
AND b.RunDate = DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate)
AND a.TempFlag = 1
WHERE b.job_id IS NULL
AND DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate) <= @CurrMaxDate
AND DATEADD(dd, 7*@i*@CurrRunEveryXWeeks, a.TempDate) >= @CurrMinDate
END

SET @i = @i + 1
END

-- Now, look for all dates between @CurrMinDate and the initial dates in #DaysAndDates.
-- We start with the dates in #DaysAndDates an subtract X weeks at a time.
SET @i = 0

WHILE DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, @tmpMaxDate) >= @CurrMinDate
BEGIN
-- Check that the min date in #JobScheduledDates is greater that the @CurrMaxDate.
-- If it is not- none of the values in that table are in the time window
-- so there's nothing to write.
IF DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, @tmpMinDate) <= @CurrMaxDate
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
FROM #DaysAndDates a
LEFT OUTER JOIN #JobScheduledDates b
ON b.job_id = @CurrJobID
AND b.schedule_name = @CurrScheduleName
AND b.RunDate = DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate)
AND a.TempFlag = 1
WHERE b.job_id IS NULL
AND DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate) <= @CurrMaxDate
AND DATEADD(dd, -7*@i*@CurrRunEveryXWeeks, a.TempDate) >= @CurrMinDate
END

SET @i = @i + 1
END

-- Move on to the next weekly schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 8
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END

-- Get the next job with a weekly schedule
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 8
AND job_id > @CurrJobID
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END


/* ==========================================================================
Step 2.3 - monthly (fixed) jobs - freq_type = 16 (freq_subday_type = 1, 4, 8)

This case is when a job runs every X months, at the Rth day of the month.
The Rth day can be the 29th, 30th, or 31st of the month, which may not exist
for some months. If the Rth day of the month indeed does not exist, then
that month is skipped.

The following logic is used to get the schedule for monthly (fixed) jobs:
Check the next run date for the job. That month is one when the job is run.
Then, add or subtract X month to find all dates in the requested time window
where the job should be run. For each month found - check whether the Rth date
of the month exist. If not- skip another X months to the next schedule.
(Checking whether the Rth day of the month exists is only done when R > 28).
========================================================================== */

DECLARE @CurrRunEveryXMonths INT -- This variable captures X.
DECLARE @CurrDayOfMonthToRun INT -- This variable captures the day codes when the job runs
-- (1-Sun, 2-Mon, ..., 7-Sat), as taken from freq_interval.


-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.

-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 16
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 16
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayOfMonthToRun = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXMonths = freq_recurrence_factor,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName

-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXMonths
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime.
-- While looping, we need to make sure that the day at which the job runs
-- indeed exists for the month (e.g., when the day is the 29th, 30th, or 31st,
-- which may not exist for some months).
SET @i = 0

-- We start by looping forward, from @CurrNextRunDate to @CurrMaxDate
WHILE DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that we need to record the calculated date.
IF DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Validate that the day to run exists for the month.
IF (@CurrDayOfMonthToRun > 28 AND DATEPART(day, DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate)) = @CurrDayOfMonthToRun)
OR @CurrDayOfMonthToRun <= 28
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

-- Now, loop backwards from @CurrNextRunDate to @CurrMinDate.
SET @i = 0

WHILE DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that we need to record the calculated date.
IF DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Validate that the day to run exists for the month.
IF (@CurrDayOfMonthToRun > 28 AND DATEPART(day, DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate)) = @CurrDayOfMonthToRun)
OR @CurrDayOfMonthToRun <= 28
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(mm, -@i*@CurrRunEveryXMonths, @CurrNextRunDate))
END
END

SET @i = @i + 1
END

-- Move on to the next monthly schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 16
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END

-- Get the next job with a monthly (fixed) schedule
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 16
AND job_id > @CurrJobID
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0
END


/* ==========================================================================
Step 2.4 - monthly (relative) jobs - freq_type = 32 (freq_subday_type = 1, 4, 8)

This case is divided into two options:
1. A job runs every X months, at the 1st, 2nd, 3rd, 4th or last day
(e.g., Sun, Mon, etc.) day of the month. Here, the day is fixed and
is associated with a day code (1-Sun, 2-Mon, ..., 7-Sat).
2. A job runs every X months, at the 1st, 2nd, 3rd, 4th or last
day of the month, weekday, or weekend day. This is a little tricky
since the last two (weekday or weekend day) depend on the datefirst
settings on the server.

The logic here is as before- we find the next_run_date, then calculate
the previous and next run dates that fall in the observed time window,
and record all dates in #JobScheduledDates.
========================================================================== */

DECLARE @CurrCalcRunDate DATETIME
DECLARE @CurrMonth INT
DECLARE @CurrYear INT
DECLARE @CurrDayCode INT
DECLARE @CurrDayCodeFor1stOfMonth INT
DECLARE @CurrDateOfFirstYthDayOfMonth DATETIME
DECLARE @CurrFirstOfMonth DATETIME
DECLARE @CurrLastOfMonth DATETIME
DECLARE @CurrScheduledDate DATETIME
DECLARE @j INT
DECLARE @k INT
DECLARE @tmpDate DATETIME



-- Type 1: Algorithm to get the next/prev scheduled run date.
-- @CurrDayToRun:
-- 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday
-- 8 = Day, 9 = Weekday, 10 = Weekend day
DECLARE @CurrDayToRun INT
-- @CurrRelativeInterval:
-- 1 = First, 2 = Second, 4 = Third, 8 = Fourth, 16 = Last
DECLARE @CurrRelativeInterval INT


-- Examples:
-- If @CurrDayToRun = 1 AND @CurrRelativeInterval = 4
-- the job runs on the third Sunday every X months.
-- If @CurrDayToRun = 9 AND @CurrRelativeInterval = 16
-- the job runs on the last weekday of the month, every X months.


-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 32
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 32
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayToRun = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXMonths = freq_recurrence_factor,
@CurrRelativeInterval = freq_relative_interval,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName


-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXMonths
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.

-- The key challenge here is to find the dates in which the job is scheduled
-- to run. We do this on a case by case basis (according to the @CurrDayToRun
-- and @CurrRelativeInterval. Only after finding the appropriate schedule we check
-- if the dates found fall in the time window.

-- First, we subtract from @CurrNextRunDate to move our starting point
-- to be earlier than @CurrMinDate.

SET @i = 0

WHILE DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) >= CAST(CAST(DATEPART(month, @CurrMinDate) AS VARCHAR(2)) + '/1/' + CAST(DATEPART(year, @CurrMinDate) AS VARCHAR(4)) AS DATETIME)
SET @i = @i - 1

-- Componesate for the last redundant subtraction.
IF @i < 0
SET @i = @i + 1


-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime.
-- The while checks that we have not passed the last day in the month of @CurrMaxDate.
WHILE DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate) <= DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(DATEPART(month, @CurrMaxDate) AS VARCHAR(2)) + '/1/' + CAST(DATEPART(year, @CurrMaxDate) AS VARCHAR(4)) AS DATETIME)))
BEGIN
-- From here on, we have an algorithm to determine the scheduled dates
-- for the job in the examined month.

-- Get the next/prev month.
SET @CurrMonth = DATEPART(month, DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate))
SET @CurrYear = DATEPART(year, DATEADD(mm, @i*@CurrRunEveryXMonths, @CurrNextRunDate))

-- Get the date for the first of the month.
SET @CurrFirstOfMonth = CAST(CAST(@CurrMonth AS VARCHAR(2)) + '/1/' + CAST(@CurrYear AS VARCHAR(4)) AS DATETIME)

-- Get the date for the last day of the month.
SET @CurrLastOfMonth = DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@CurrMonth AS VARCHAR(2)) + '/1/' + CAST(@CurrYear AS VARCHAR(4)) AS DATETIME)))



IF @CurrDayToRun BETWEEN 1 AND 7
BEGIN
-- Get the day code for the first day of the month.
SET @CurrDayCodeFor1stOfMonth = CASE DATENAME(dw, @CurrFirstOfMonth) WHEN 'Sunday' THEN 1 WHEN 'Monday' THEN 2 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 4 WHEN 'Thursday' THEN 5 WHEN 'Friday' THEN 6 WHEN 'Saturday' THEN 7 END
-- Get the date of the first encounter of the day in @CurrDayToRun for the month.
SET @CurrDateOfFirstYthDayOfMonth = DATEADD(dd, (@CurrDayToRun + 7 - @CurrDayCodeFor1stOfMonth) % 7, @CurrFirstOfMonth)

-- Get the date in which the job is scheduled to run.
SET @CurrScheduledDate = CASE @CurrRelativeInterval
WHEN 1 THEN @CurrDateOfFirstYthDayOfMonth
WHEN 2 THEN DATEADD(dd, 7, @CurrDateOfFirstYthDayOfMonth)
WHEN 4 THEN DATEADD(dd, 2*7, @CurrDateOfFirstYthDayOfMonth)
WHEN 8 THEN DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
-- Last day Y of the month.
-- There are no more than 5 encounters of day Y per month.
-- The last day Y can therefore be DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
-- or DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth).
WHEN 16 THEN
CASE WHEN DATEPART(month, DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth)) <> @CurrMonth
THEN DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
ELSE DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth)
END
END
END
ELSE
BEGIN
-- This is the case where @CurrDayToRun is between 8 and 10.

IF @CurrDayToRun = 8 -- Job runs on the 1st - 4th or last day of month.
BEGIN
SET @CurrScheduledDate = CASE @CurrRelativeInterval
WHEN 1 THEN @CurrFirstOfMonth
WHEN 2 THEN DATEADD(dd, 1, @CurrFirstOfMonth)
WHEN 4 THEN DATEADD(dd, 2, @CurrFirstOfMonth)
WHEN 8 THEN DATEADD(dd, 3, @CurrFirstOfMonth)
WHEN 16 THEN @CurrLastOfMonth
END
END

IF @CurrDayToRun IN (9, 10) -- Job runs on the 1st - 4th or last weekday or weekend day of the month.
BEGIN
IF @CurrRelativeInterval IN (1, 2, 4, 8)
BEGIN
SET @tmpDate = @CurrFirstOfMonth

SET @k = 0

-- @j holds the Nth encounter of a business or weekend day.
SET @j = CASE @CurrRelativeInterval
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 4 THEN 3
WHEN 8 THEN 4
END

SET @CurrScheduledDate = NULL

WHILE @CurrScheduledDate IS NULL
BEGIN
-- Here we check if we need to track week or weekend days
-- and count when they are encountered.
IF ( @CurrDayToRun = 9 AND ( (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) NOT IN (5, 6)) )
OR ( @CurrDayToRun = 10 AND ( (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) IN (5, 6)) )
-- Track the fact that a day of the desired type is encountered.
SET @k = @k + 1

-- Loop to the next date unless the @tmpDate is already the Nth encounter.
IF @k < @j
SET @tmpDate = DATEADD(dd, 1, @tmpDate)
ELSE
SET @CurrScheduledDate = @tmpDate
END
END
ELSE
BEGIN
-- Here we are looking for the last business or weekend day for the month.
SET @tmpDate = @CurrLastOfMonth

SET @CurrScheduledDate = NULL

WHILE @CurrScheduledDate IS NULL
BEGIN
-- Check if the job runs on the last business day and the last date of the month
-- is indeed a business day, or if the job runs on the last weekend day
-- and the last day is indeed a weekend day
IF (@CurrDayToRun = 9 AND (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) NOT IN (5, 6) )
OR (@CurrDayToRun = 10 AND (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1)) % 7) IN (5, 6) )

SET @CurrScheduledDate = @tmpDate
ELSE
SET @tmpDate = DATEADD(dd, -1, @tmpDate)
END
END
END
END


-- Here we have the @CurrScheduledDate - date of the month when the job
-- is scheduled to run. Check that we need to record the calculated date before we do so.
IF @CurrScheduledDate BETWEEN @CurrMinDate AND @CurrMaxDate
BEGIN
INSERT INTO #JobScheduledDates (job_id, schedule_name, RunDate, StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
@CurrScheduledDate,
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))) )
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS(SELECT TOP 1 * FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = @CurrScheduledDate )
END

SET @i = @i + 1

END

SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 32
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL AND next_run_date > 0

END

SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 32
AND job_id > @CurrJobID
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL AND next_run_date > 0
END


/* ========================================================================
Step 3 - Find the times at which the job is scheduled to run,
for all dates found in Step 2 above.

In Step 2 we found all the dates in which the job was scheduled to run.
For all those dates, we now find the scheduled times.
====================================================== */

-- In #JobScheduledDates we already recorded the first time of the day
-- in which jobs are run (column StartTimeEx). First, we update
-- that table to get the number of seconds from the start of the day
-- for the first run (column StartTimeInSecFromStartOfDay).

UPDATE #JobScheduledDates
SET StartTimeInSecFromStartOfDay = CAST(LEFT(StartTimeEx, 2) AS INT)*3600
+ CAST(SUBSTRING(StartTimeEx, 3, 2) AS INT)*60
+ CAST(RIGHT(StartTimeEx, 2) AS INT)


-- Run times have to be constructed for all jobs that run
-- daily, weekly, or monthly. Also, if a job is scheduled
-- run once per day instead of every X minutes or hours,
-- then we already have the run time in #JobScheduledDates.

-- In order to distinguish between the run-dates and run dates & times,
-- we create a new temp table that holds the date + time info.

IF OBJECT_ID('tempdb..#JobScheduledDatesAndTimes') IS NOT NULL
DROP TABLE #JobScheduledDatesAndTimes

CREATE TABLE #JobScheduledDatesAndTimes (
job_id VARCHAR(64),
schedule_name NVARCHAR(128),
RunDateTime DATETIME,
ConflictingRunDateTime DATETIME)

EXEC('CREATE UNIQUE CLUSTERED INDEX CI_#JobScheduledDatesAndTimes_' + @sSPID + '
ON #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime) ')

-- The scheduled times are determined as follows (for daily, weekly, and monthly jobs):
-- freq_subday_type:
-- 1 - run once (not interesting. we already have the run-time for this case).
-- 4 - run every X minutes. X is stored in freq_subday_interval.
-- 8 - run every Y hours. Y is stored in freq_subday_interval.

-- It is now left to calculate the run times.

-- We start by populating #JobScheduledDatesAndTimes with all jobs
-- that run once.
INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
SELECT b.job_id,
b.schedule_name,
DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b
ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type NOT IN (4, 8)
AND DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider

-- Next, we handle all jobs that are scheduled to run every X minutes in a day.
-- Here, we simply add @i x X minutes for all schedules at once until
-- we cover the entire day.

DECLARE @Cnt INT

SET @i = 0
SET @Cnt = 1

WHILE @Cnt > 0
BEGIN
INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
SELECT b.job_id, b.schedule_name, DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b
ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type = 4
-- We need to make sure that we don't cross over to the next day.
AND DATEPART(day, DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)) = DATEPART(day, b.RunDate)
-- Also consider the min/max_datetime_to_consider
AND DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, @i*60*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider

SET @Cnt = @@ROWCOUNT
SET @i = @i + 1
END

-- Finally, we do the same as above for all jobs that run every Y hours.

SET @i = 0
SET @Cnt = 1

WHILE @Cnt > 0
BEGIN
INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
SELECT b.job_id, b.schedule_name, DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b
ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type = 8
-- We need to make sure that we don't cross over to the next day.
AND DATEPART(day, DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)) = DATEPART(day, b.RunDate)
-- Also consider the min/max_datetime_to_consider
AND DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider

SET @Cnt = @@ROWCOUNT
SET @i = @i + 1
END



/* ========================================================================
Step 3 - Find conflicting job schedules in the requested time window.

Here, we compare the run date-time values found for each schedule
in #JobScheduledDatesAndTimes to see if there is a time gap
lower than @TimeGapInSeconds between job schedules.
====================================================== */

IF OBJECT_ID('tempdb..#ConflictingSchedules') IS NOT NULL
DROP TABLE #ConflictingSchedules

SELECT a.job_id AS JobID,
(SELECT TOP 1 job_name FROM #JobScheduleInfo WHERE job_id = a.job_id) AS JobName,
a.schedule_name AS ScheduleName,
a.RunDateTime AS ScheduledDateTime,
b.schedule_name AS ConflictingScheduleTimeScheduleName,
b.RunDateTime AS ConflictingScheduleDateTime
INTO #ConflictingSchedules
FROM #JobScheduledDatesAndTimes a
INNER JOIN #JobScheduledDatesAndTimes b
ON a.job_id = b.job_id
AND a.schedule_name <> b.schedule_name
AND a.RunDateTime >= DATEADD(ss, -@TimeGapInSeconds, b.RunDateTime)
AND a.RunDateTime <= b.RunDateTime


-- Remove duplicates from #ConflictingSchedules
-- If Schedule1 and Schedule2 conflict, then #ConflictingSchedules
-- will report the Schedule1 conflicts with 2 and that 2
-- conflicts with 1. The DELETE statement below removes
-- these duplicates.
DELETE a
FROM #ConflictingSchedules a
INNER JOIN #ConflictingSchedules b
ON a.ScheduleName = b.ConflictingScheduleTimeScheduleName
AND a.ScheduledDateTime = b.ConflictingScheduleDateTime
AND a.ConflictingScheduleTimeScheduleName = b.ScheduleName
AND a.ConflictingScheduleDateTime = b.ScheduledDateTime
WHERE a.ScheduleName > a.ConflictingScheduleTimeScheduleName



SELECT *
FROM #ConflictingSchedules
ORDER BY ScheduledDateTime



-- Cleanup - drop temp tables used in the script.
IF OBJECT_ID('tempdb..#JobScheduleInfo') IS NOT NULL
DROP TABLE #JobScheduleInfo

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results

IF OBJECT_ID('tempdb..#JobScheduledDates') IS NOT NULL
DROP TABLE #JobScheduledDates

IF OBJECT_ID('tempdb..#DaysAndDates') IS NOT NULL
DROP TABLE #DaysAndDates

IF OBJECT_ID('tempdb..#JobScheduledDatesAndTimes') IS NOT NULL
DROP TABLE #JobScheduledDatesAndTimes

IF OBJECT_ID('tempdb..#ConflictingSchedules') IS NOT NULL
DROP TABLE #ConflictingSchedules