IF OBJECT_ID('tempdb.dbo.#CalendarView', 'U') IS NOT NULL
DROP TABLE #CalendarView;
go
Create Table #CalendarView(CalendarDate DATE);
GO
DECLARE @StartDate DATE = '20160101', @NumberOfYears INT = 30;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
INSERT INTO #CalendarView(CalendarDate)
SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) AS x
) AS y;
SELECT * from #CalendarView
go
--Create a job and keep it not with schedule.Now, execute your job in below query:
DECLARE @Today DATE = GETDATE();
DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
DATEADD(DAY, 1 - DAY(@Today), @Today));
DECLARE @LastBut1DayThisMonth DATE= DATEADD(DD, -2,
DATEADD(MM,
DATEDIFF(MM, 0,
CONVERT(VARCHAR, GETDATE(), 101))
+ 1, 0));
--PRINT @Today;
--PRINT @FirstDayNextMonth;
--PRINT @LastBut1DayThisMonth;
SELECT MAX(CalendarDate)
FROM #CalendarView
WHERE CalendarDate = @LastBut1DayThisMonth;
IF @LastBut1DayThisMonth = @Today
BEGIN
EXEC msdb.dbo.sp_start_job 'BACKUPTEST';--I have to create a Job which kicks SSIS package with no schedule
END;
No comments:
Post a Comment