SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Custom Scheduling?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

binsel
Starting Member

Canada
5 Posts

Posted - 10/24/2013 :  12:39:09  Show Profile  Reply with Quote
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:Weekly
Time int, --1800 means 6pm
Days 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

India
52317 Posts

Posted - 10/24/2013 :  13:16:10  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

binsel
Starting Member

Canada
5 Posts

Posted - 10/24/2013 :  22:49:32  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/25/2013 :  02:53:53  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000