Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Self Scheduling Jobs


Self Scheduling Jobs

Author
Message
richard.noordam
richard.noordam
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 442
Comments posted to this topic are about the item Self Scheduling Jobs
Michael G
Michael G
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 154
Thanks for the tips. Nice to have a framework the supplements the standard jobs that ship with SQL.

Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. :-D
David Data
David Data
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 810
Good grief, that's a complicated way to do it. As you had to write .NET code anyway, why not do the whole thing in a single script task, (or two if you wanted to use a separate SSIS mail task for error messages rather than write code to send an email)? Simply run the SQL commands within the .NET code. If anyone doesn't know how to do that, Google for examples; SqlCommand.ExecuteScalar to get the scheduling date and SqlCommand.ExecuteNonQuery to run sp_add_schedule.

Alternatively, you could probably do it all in one SQL procedure.

I did find the article useful though, as I didn't know about msdb.dbo.sp_add_schedule before.
richard.noordam
richard.noordam
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 442
The purpose of this example was a demonstration of individual steps for clarity.

Yes it could of very easily been done in less steps.
Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 944
If the job fails to schedule its next occurrence then the job never runs again? So for quarterly jobs, you won't find out about the failure for at least 3 months and if it languishes in a broken state for as much as 6 months you could have serious consequences of a skipped job? (or adjust the timings for whatever interval; long or short it's a similar problem)

I would not want to be the person to inherit your role. Upon first-glance inspection at what is immediately visible, the jobs are all set to run once. There also exists some proprietary control tables that don't seem to be used in any business division. Until I dig into the package to discover the self-scheduling feature, it isn't obvious there even IS a schedule.

I guess basically I'm curious why it's better to roll-your-own than to use the native scheduling feature that anyone educated in SQL Server would already understand.
richard.noordam
richard.noordam
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 442
Mike Dougherty-384281 (6/20/2012)
If the job fails to schedule its next occurrence then the job never runs again? So for quarterly jobs, you won't find out about the failure for at least 3 months and if it languishes in a broken state for as much as 6 months you could have serious consequences of a skipped job? (or adjust the timings for whatever interval; long or short it's a similar problem)


Nope, the emails tell you of successes and failures. In no way does this relieve you of your monitoring responsibility. All failures would require corrective action.



I would not want to be the person to inherit your role. Upon first-glance inspection at what is immediately visible, the jobs are all set to run once. There also exists some proprietary control tables that don't seem to be used in any business division. Until I dig into the package to discover the self-scheduling feature, it isn't obvious there even IS a schedule.


Really? you wouldn't want to open up a Sharepoint WorkGroup site with it all documented, the who, what, where, when etc? and then be able to go straight to the SSIS package, job history for review? Maybe you have been the victim of poor documentation. If you inherited my role, this would NOT be the case.... did i mention that it's all searchable too?

The proprietary control tables do exist in a lot of places, and educational institutions for certain have, and maintain these regularly.


I guess basically I'm curious why it's better to roll-your-own than to use the native scheduling feature that anyone educated in SQL Server would already understand.


This most definitely is NOT a replacement for scheduling but an 'addon' to handle cases where the native scheduling isn't as flexible as is necessary to the specific case at hand.
jstariha
jstariha
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 204
Nice work. I don’t think I'd make it as complex, but I like it, I put in the briefcase for future reference. Thanks!



Joe Stariha | Magenic – North Region | http://magenic.com/ | http://magenic.com/Blog.aspx
Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 944
richard.noordam (6/20/2012)
Mike Dougherty-384281 (6/20/2012)
[quote]
I would not want to be the person to inherit your role.


Really? you wouldn't want to open up a Sharepoint WorkGroup site with it all documented, the who, what, where, when etc? and then be able to go straight to the SSIS package, job history for review? Maybe you have been the victim of poor documentation. If you inherited my role, this would NOT be the case.... did i mention that it's all searchable too?

The proprietary control tables do exist in a lot of places, and educational institutions for certain have, and maintain these regularly.


Victim of poor documentation and sub-optimal operational practices. I forget that not every environment works this way.


I guess basically I'm curious why it's better to roll-your-own than to use the native scheduling feature that anyone educated in SQL Server would already understand.


This most definitely is NOT a replacement for scheduling but an 'addon' to handle cases where the native scheduling isn't as flexible as is necessary to the specific case at hand.


That's a good point. I didn't see that aspect of it from the original article. If/when there's a next article you could give more time to the WHY as much as the HOW. (Backstory helps frame the context for clever solutions)

Thanks
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 343
Agreed with SSC-Enthusiastic. This solution is very danger to implement. Especially in environments where multiple DBAs and database developers controlling jobs in a heavy processing environment. If for some reason error email is missed (or action is not taken because users are not complaining) the price will be very heavy for missing the next run. E.g. if I can use pliers to hit the nail it does not mean that this is right tools for the job. So, I would like to know why this solution was implemented.



richard.noordam
richard.noordam
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 442
Leo Peysakhovich (6/20/2012)
Agreed with SSC-Enthusiastic. This solution is very danger to implement. Especially in environments where multiple DBAs and database developers controlling jobs in a heavy processing environment. If for some reason error email is missed (or action is not taken because users are not complaining) the price will be very heavy for missing the next run. E.g. if I can use pliers to hit the nail it does not mean that this is right tools for the job. So, I would like to know why this solution was implemented.


Your example is most relevant, specifically, i don't need a crane to nail two 2x4's together either.

I'm pretty sure that the idea of 'universal solutions'/one size fits all is a myth. While it may not be the pathway you want to use in large installations, most SQL instances are not, and thus might have a place.

as to 'missing' emails, we have more than one recipient as a backup, and i did state that this does not remove responsibility for not only paying attention to your emails, but also to any job failures on your system (per post above). One OR the other should prompt you to do something about the problem/failure. Also failure notification would be immediate, giving you some time to determine what caused the issue and resolve it, long before it would be 'due'.

The only thing that changed with this implementation, was the jobs self schedule instead requiring some sort of manual kick off.

and as to why, succinctly:
This is a scheduling 'addon' to handle cases where the native scheduling isn't as flexible as is necessary to the specific varying periodicity of this type of data load.

i seemed to have rebutted his issues to his satisfaction, with just a minor request for further background information. what part did i miss with you?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search