May 21, 2009 at 12:24 pm
Friends, I have two questions and since they both are related, I am posting it in the same posting.
Question one:
I have a job set up which has 10 + steps on SQL 2005. Question is – is there a way to schedule this job for 21st day of the month and then run daily until end of that month?
Then next month again it will run on 21st till end of the month…this schedule goes on indefinitely? I check scheduler and all I see is it can be run daily, monthly, weekly or on a particular day. Not sure how to achieve this. Mat be a DOS command script? I have to admit my DOS is rusted.
Question two.
In the job I have several steps and in first few steps dates are calculated and depending on this dates a call is made to a stored proc
Eg: @Fromdate =
@Todate =
@CallSpDate =
@Today =
Logic
If @Today = @CallSpDate
Call Stored Proc
Else Raise error
Question is all the above mentioned dates remain same for four steps. Currently I am doing these calculations in every step and then based on that a decision is made. I want to keep the steps separate for clarity, readability and troubleshooting.
I know I can combine all the steps and call stored proc one after other but I do not want to do that. I can also write a function and call this function in every step to retrieve calculated dates. But I don’t think this will be efficient. (my thoughts).
Is there a way to pass on the calculated dates from ste1 to step2, step3 and so on…?
TIA,
Natasha
May 21, 2009 at 1:27 pm
You can set a step to go to the next step on success, stop on failure. Set up a starting step to check the day of the month. If it's 21 or greater, return something valid, like a SELECT 1. If it's not, then raise an error. That should fail the step and allow you to quit.
Schedule it every day. It will appear to fail 20 days of the month, but you can ignore that.
May 21, 2009 at 2:17 pm
Steve,
Thank you for your response. I am already doing that in my job steps.
If @Today = @CallSpDate
Call Stored Proc
Else Raise error
I was looking for an elegant way to schedule it instead of scheduling everyday. I know it is a weired demand. But our business requirement is to capture data for the last week of a month only 😉
If @Today = @CallSpDate
Call Stored Proc
Else Raise error
May 21, 2009 at 2:20 pm
Ah, sorry don't have a better way. Was thinking putting it in one step would help.
I've never heard of this type of scheduling. I guess you could simplify it by using one job to check and having it call another job if needed.
May 21, 2009 at 5:30 pm
I don't know if this is more elegant, but you could create a separate monthly schedule for each day from the 21st thru the 31st. That would mean you'd have 11 schedules for the job, each one firing one day a month.
Greg
May 21, 2009 at 5:37 pm
Didn't think of that, Greg, and it's not bad. It is maintenance if you change the job, though you could abstract things with stored procedures.
Adding/removing a day means just adding or removing one job. I kind of like that solution.
May 22, 2009 at 6:50 am
Thank you for the suggestion. I kind of like the idea of a separate schedule that will fire only one day of the month.
Any ideas on how to pass the date variables from one step to other instead of calculating at every step since they all the same calculated values. My steps and everything is working correctly. But you know how it is one is always thriving for a better / elegant solution.
TIA,
Natasha
May 22, 2009 at 9:48 am
I actually don't think it's all that inefficient to calculate the date in each step, but you could calculate it in the first step and write it to a table which the following steps could query.
Greg
May 22, 2009 at 9:59 am
I don't think it's more or less efficient storing the value in a table.
Calculating it is a function call, no I/O, minimal CPU to perform.
The query writing to a table would create a log write, and a data write at some point. The info, one row, one col, would likely remain in memory for the QP to access, but I'd think that requires more resources than a function call for scalar values.
I'd go with Greg's suggestion, separate schedule for every day.
May 22, 2009 at 11:57 am
Greg & Steve,
Thank you for suggestions and spending time analyzing do & dont's. I have decided to go with Greg's suggestion of scheduling job and keep my logic in the steps of calulating dates the same.
Thanks again,
Nat
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply