Friday, September 23, 2016

JOB to run last day minus 1 day of the month



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

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...