Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
binsel
Starting Member
5 Posts |
 Posted - 2013-10-24 : 12:39:09
|
| Hi All,I am working on a custom scheduling process and have a few rough ideas in my mind but I am not sure what is a scalable, simple, defect-free solution. I plan to have one table, named ProcessSchedule, and one Select statement within a sproc scheduled to run every minute as a job through SQL Agent. There will be many processes coming into ProcessSchedule. The sproc's job is to query the table every minute and determine which process(es) are ready to run at that minute (granular at minute level).PROCESSSCHEDULE Table:ProcessID GUID,Frequency int, -- 1:Once 2:Hourly 3:Daily 4:WeeklyTime int, --1800 means 6pmDays int, --Mon:1 Tue:2 Wed:4 Thu:8, so 9 is Mon+Thu* I don't want to use char in WHERE clause to make it sargable* There might be additional 3 computed columns like FreqDesc, TimeDesc, DaysDesc in string datatypes* If SQL Agent stops for a while (like a service restart), ideally, the SELECT should pick up where it's left off. Thanks for any ideas,Kuzey |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-24 : 13:16:10
|
| I think you need a job to execute the procedure first.Then in procedure retrieve the processes from table with Time in the current time slot and not started yet. (You may need a status field in table to indicate if jobs have started, completed or in pending state). Based on Frequency value this field has to be reset for each time run.Then use sp_start_job to kick off the job. At the end check if job was success (use msdb catalog view jobhistory) and then update status field accordingly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
binsel
Starting Member
5 Posts |
Posted - 2013-10-24 : 22:49:32
|
| Thanks, Visakh.I was thinking a stored proc runs as job every minute.Why do I need to use sp_start_job? Do you mean two jobs; first one kicks off second one using sp_start_job?Kuzey |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-25 : 02:53:53
|
| yes... If you want to kick off jobs based on your custom schedule you'll need a call tp sp_start_job from within your procedure------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Custom Scheduling?