SQL Server 2012 Agent Job & stored procedure issue URGENT
-
Could you kindly explain please, I am not sure about the mod with year component? I am rather saying I don't quite understand please. I still don't know how to get the second issue to call the stored procs every two years as SQL Agent Job doesn't seem to offer the solution.
A simple search for TSQL Mod would have given you the information. However this may help, this year return 0, next year 1.
SELECT DATEPART(YEAR,GETDATE()) % 2
SELECT DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())) % 2
As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.
Never underestimate the power of human stupidity RAH
-
A simple search for TSQL Mod would have given you the information. However this may help, this year return 0, next year 1.
SELECT DATEPART(YEAR,GETDATE()) % 2
SELECT DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())) % 2
As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.
Never underestimate the power of human stupidity RAH
Does this look efficient and OK? DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1) SET @GetHour = datepart(hour, GETDATE()) -- returns hour --Get Today date to compare to the last day of December in current year IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20 BEGIN --If Equal - then increase the next 2 years SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss') END ELSE SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss') PRINT @NextNewDate WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate) BEGIN Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month." How do you make it meet your criteria i.e. if it falls on 31st December of each year?
-
A simple search for TSQL Mod would have given you the information. However this may help, this year return 0, next year 1.
SELECT DATEPART(YEAR,GETDATE()) % 2
SELECT DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())) % 2
As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.
Never underestimate the power of human stupidity RAH
-
Hi all, I am in desperate need of help please. I have created a stored procedure with a while loop using a hard coded date 31-12-2014 to compare against current date produced in a while loop. I need to make the hard coded date reflect every two years i.e. 31-12-2016, 31-12-2018 etc. How can the date be automatic instead of hard coded please? i.e While (currdate <= '31-12-2014') Second issue, how do I make the stored procedure to run every two years as well? SQL Agent Job doesn't have a yearly frequency at all. Can someone please offer an example coding and suggestions to those two issues which I will be grateful please. :confused: Thank You!
You could set the job to recur every 730 days, or 104 weeks, though this wouldn't take in to account leap years or the fact that there are 52 weeks + 1 or 2 days in each year
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Hi Again "so run the job every month, if it does not meet your criteria then let it wait for next month." Would it mean I have to somehow implement a code within the Job Step List in the SQL Agent Job? Many Thanks
Seuss wrote:
implement a code within the Job Step List in the SQL Agent Job
No your job is calling a procedure. In the procedure I would first check that the current date matches the next process date and if so has the process been run for this date. If it fails either test then exit without processing.
Never underestimate the power of human stupidity RAH
-
Seuss wrote:
implement a code within the Job Step List in the SQL Agent Job
No your job is calling a procedure. In the procedure I would first check that the current date matches the next process date and if so has the process been run for this date. If it fails either test then exit without processing.
Never underestimate the power of human stupidity RAH
Ah I think I understand what you are saying, have the SQL Job Agent set up and each month - when it calls the stored procedure - if the dates don't match up - it needs to exit out of the SQL Job Agent? I have also been googling and there were some examples where you could implement a job step code to do it? Not sure if this is feasible or a good idea? Huge thanks to you for your patience. I know I am a bit persistent on this matter :-)
-
You could set the job to recur every 730 days, or 104 weeks, though this wouldn't take in to account leap years or the fact that there are 52 weeks + 1 or 2 days in each year
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Seuss wrote:
implement a code within the Job Step List in the SQL Agent Job
No your job is calling a procedure. In the procedure I would first check that the current date matches the next process date and if so has the process been run for this date. If it fails either test then exit without processing.
Never underestimate the power of human stupidity RAH
I have created a step 1 in the SQL Agent Job: DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1) SET @GetHour = datepart(hour, GETDATE()) -- returns hour --Get Today date to compare to the last day of December in current year IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT('2014-05-28', 'yyyy-MM-dd')) AND (@GetHour >=15) BEGIN SELECT 2 END ELSE EXEC msdb.dbo.sp_stop_job @job_name='Testjob' Then created step 2 to call the stored procedure I have created I then created a scheduler to test it out with today date starting from 11am and every 5 mins with recurring mode. For some reason, it nevers call the stored proc which should have from step 1 to step 2 Any ideas please?
-
Does this look efficient and OK? DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1) SET @GetHour = datepart(hour, GETDATE()) -- returns hour --Get Today date to compare to the last day of December in current year IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20 BEGIN --If Equal - then increase the next 2 years SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss') END ELSE SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss') PRINT @NextNewDate WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate) BEGIN Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month." How do you make it meet your criteria i.e. if it falls on 31st December of each year?
If you want to excecute your query on every 31st December add a below condition
IF (MONTH(GETDATE()) = 12 AND DAY(GETDATE()) = 31)
BEGIN
/*
This will excecute on every 31'st December
Write down your query
*/
END -
Does this look efficient and OK? DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1) SET @GetHour = datepart(hour, GETDATE()) -- returns hour --Get Today date to compare to the last day of December in current year IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20 BEGIN --If Equal - then increase the next 2 years SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss') END ELSE SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss') PRINT @NextNewDate WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate) BEGIN Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month." How do you make it meet your criteria i.e. if it falls on 31st December of each year?
If you want to excecute your query on every 31st December add a below condition
IF (MONTH(GETDATE()) = 6 AND DAY(GETDATE()) = 16)
BEGIN
--Write down your query
END