April 10, 2012 at 1:18 pm
I have an application that i've written for the Community College I work for, It allows students to create an educational class plan basing it on what we've offered in classes for the previous 2 years.
I need to load the EdPlan_CourseExtended table on varying dates in the future corresponding to changes per quarter. (so at the beginning of the quarter, it runs, and then done.)
Here is the quandry, as we know, college quarter start dates vary all through the year and aren't the same even year to year. (obvious yes). So how do i schedule it to fill that table?
I can see 3 solutions:
a) run a job daily to load the table daily based on the current quarter, simple, but inefficient. lots of extra work done. not what i really want.
b) run a job daily to load the table to look at the max yearqtr indicator, based on dates and see if we have the current quarter, tedious and seems a bit too much work.
c) when the job first runs, it determines the current quarter, and it's max quarter date, and then uses that to scheudule it's next running date. so as a last step, after filling the job, it schedules its next run for it's next 'needed' date, itself.
I think i need to use: sp_add_schedule do accomplish that.
from microsoft docs:
EXEC dbo.sp_add_schedule
@schedule_name = N'MyJob',
@freq_type = 1,
@active_start_time = 233000 ;
GO
do i need start time? or if scheduled for a specific date with [ @active_start_date = ] active_start_date
but maybe:
EXEC dbo.sp_add_schedule
@schedule_name = N'MyJob',
@freq_type = 1,
@active_start_date = @ScheduledDate ;
GO
where @ScheduledDate is the next needed date.
so am i going down the proper approach? I don't have a lot of time to squander on doing this here is why I'm asking and getting a response so I don't go down the wrong pathway, and waste what little time i have for this already.
I'm going thru and documenting, in a workgroup sharepoint site all the applications, using a wiki type format, that i've written in the last almost 4 years. In case i either get hit by bus (more likely) or win the lotterly(less likely) and no longer have to work for my living.... on a positive note, it is forcing me to 'finish' those last few pesky details before i forget about them again.... while providing a better product for my customers. 🙂
thanks a bunch!
April 10, 2012 at 2:06 pm
what about a calender table. add the dates to run to the calendar table and have a job that runs each day check the table if the current date is equal to any date in the calendar table run the upload. then you just add the dates to the calendar table as you get them. could also have a job that runs to query some other table and add the date to the calendar table. (like an administrative table that lists course start and end dates or something similar.)
EDIT: or is that option 2 in your suggested options and i just misread it?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 10, 2012 at 2:27 pm
yes this is option two. :-). I think I'm looking for the 'minimal' approach here. or basically, the least # of times i have to run the job the better.
Is scheduling the next job during a run, even a good idea? scripts scheduling themselves makes me nervous for some reason, and I can't put a finger on it. probably just fine, but that is what I was asking... should scripts schedule their next run (by similar logic as #2, where you would have a true statement) but within the script?
April 10, 2012 at 2:34 pm
richard.noordam (4/10/2012)
yes this is option two. :-). I think I'm looking for the 'minimal' approach here. or basically, the least # of times i have to run the job the better.Is scheduling the next job during a run, even a good idea? scripts scheduling themselves makes me nervous for some reason, and I can't put a finger on it. probably just fine, but that is what I was asking... should scripts schedule their next run (by similar logic as #2, where you would have a true statement) but within the script?
with the calendar table you can put the logic in your job to schedule the next run on the next date in your calendar table so you would not have to have it run every day. and as long as you test the hell out of it in dev i have done something similar. still makes me leery and i have an email sent to me and double check the next run time but dont have to worry about making sure to set it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 10, 2012 at 2:51 pm
yea, am a paranoid.... and feel leery of this solution. Self scheduling scripts.... sigh, just makes me nervous.
I would most definitely add an email message and destination for documentation, have such a folder in mailbox now, with rules to hide all 'successes' (non interesting), and then I look at the daily loads and any failures, and then check the job monitor, to be safe to see actives, and their status.
So you are fine with this type of approach assuming, that there is a way to document it that it indeed happened, success or failure (via email message) in the job?
the calendar table you are referencing for us will be the Quarter_Metadata table. has all sort of information related to the quarter, that are important to the college and various processes.
I could probably automate this way very effectively, as a lot of processes are dependant on the quarter start/end date which would be a job specific piece to change (+/- like 10 days to quarter depending on what was checked).
April 10, 2012 at 3:03 pm
im fine with things scheduling themselves with in reason. the first is i have tested them in dev and been able to break them in dev doing things that i see some times in the DB and then find ways to deal with the exceptions. the second is an email on success or failure and then double check when the email comes in. im horrible at remembering to schedule things but when i have a reminder to check that something was done i go and check it when the reminder pops up.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 10, 2012 at 3:03 pm
I think i need to use: sp_add_schedule do accomplish that.
from microsoft docs:
EXEC dbo.sp_add_schedule
@schedule_name = N'MyJob',
@freq_type = 1,
@active_start_time = 233000 ;
GO
do i need start time? or if scheduled for a specific date with [ @active_start_date = ] active_start_date
but maybe:
EXEC dbo.sp_add_schedule
@schedule_name = N'MyJob',
@freq_type = 1,
@active_start_date = @ScheduledDate ;
GO
where @ScheduledDate is the next needed date.
so maybe something like this:
EXEC dbo.sp_add_schedule
@schedule_name = N'MySchedule',
@freq_type = 1,
@active_start_date = @ScheduledDate ;
GO
EXEC sp_attach_schedule
@job_name = N'MyJob',
@schedule_name = N'MySchedule' ;
GO
(very similar to what's in the ms docs.)
?
so do I need a time too? or is midnight assumed?
April 10, 2012 at 3:05 pm
yep with something like the following to pull the next date.
SELECT @NextDate = MIN([datecolumn])
FROM [datetable]
WHERE [datecolumn] > getdate()
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 10, 2012 at 3:08 pm
Thanks!
will go forth with distrust in my heart, and suspicion in my actions ;). am paranoid a bit when it comes to my data....
April 10, 2012 at 3:09 pm
richard.noordam (4/10/2012)
Thanks!will go forth with distrust in my heart, and suspicion in my actions ;). am paranoid a bit when it comes to my data....
what?? a dba paranoid about data loss?? i dont think i have ever met one of those. 🙂
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply