April 6, 2010 at 7:11 am
Since there's no way to schedule a job for the last day of the month, I've been working on a method to work around this. I've searched and googled and found a piece here and a piece there, but can't pull them all together. All I'm trying to do is on the last day of the month, run 3 stored procs that basically save a copy of 3 tables as of that date.
From what I can read, you can't run a stored proc from inside a Case statement. The only thing I can think of is to have a daily job do some date calculations and if it's the last day of the month, run the 3 sprocs. Is there way I can get this to run?
Here are my date calcs:
DECLARE @Today DATETIME, @LastDay DATETIME
SET @Today = CONVERT(VARCHAR(10), GETDATE(), 111)
SET @LastDay = CONVERT(VARCHAR(10), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)), 111)
and here are the 3 sprocs that would ideally be run:
EXEC Archive.dbo.Archive_Organization
EXEC Archive.dbo.Archive_SIDPERS
EXEC Archive.dbo.Archive_Strength
Any help/pointers at all would be greatly appreciated.
April 6, 2010 at 9:17 am
Use IF...ELSE
http://msdn.microsoft.com/en-us/library/ms182717(SQL.90).aspx
My month end check is a little different
declare @currentdate char(8)
declare @lastmonthdate char(8)
SET @currentdate = (SELECT convert(char(8), getdate(), 112))
SET @lastmonthdate = (SELECT convert(char(8),DATEADD(dd, -DAY(DATEADD(m,1,getdate())), DATEADD(m,1,getdate())), 112))
PRINT @currentdate
PRINT @lastmonthdate
IF @currentdate = @lastmonthdate
BEGIN
EXEC Archive.dbo.Archive_Organization
EXEC Archive.dbo.Archive_SIDPERS
EXEC Archive.dbo.Archive_Strength
END
April 6, 2010 at 9:26 am
If you pick Monthly on the schedule form in a job, you can pick last day of every 1 month as an option. Will that not do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2010 at 9:31 am
GSquared (4/6/2010)
If you pick Monthly on the schedule form in a job, you can pick last day of every 1 month as an option. Will that not do what you need?
I have to agree with Gus on this one. I just checked it out and that is how I would do it. No need to mess with date calculations.
April 7, 2010 at 10:24 am
Thank you gentlemen, I learned something new today. Much preferrable to the date calculation method.
April 7, 2010 at 11:22 am
I appreciate the heads up. I don't have the access to check this for myself. I was going on what I was told. I'm working with SQL Server Express on my desktop as my testbed. The Oracle DBA who manages our SQL 2005 server (3 hours away) said he went thru and didn't see that option. I'll be getting with him this weekend to verify. Thanks again.
April 7, 2010 at 12:37 pm
SQL Server Express does not have SQL Server Agent itself to run jobs. So, if you want to run a script periodically for SQL Server Express, then you will need to write the script and then use windows task scheduler to run the script using SQLCMD. Task Scheduler however has the option of running a script on the last day of the month.
April 9, 2010 at 6:10 am
This is not related to scheduling.but can anyone tell me how to get start date of the month too.
Thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply