Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Unanswered: Custom Scheduling

    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 3aily 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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why aren't you using the time data type?



    > I don't want to use char in WHERE clause to make it sargable

    Can you elaborate please?

    > If SQL Agent stops for a while (like a service restart), ideally, the SELECT should pick up where it's left off.

    In that case it needs to record when it last ran.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you recreating the SQL Agent wheel?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2013
    Posts
    4

    Custom Scheduling

    >Why aren't you using the time data type?
    I don't need seconds and milliseconds and using integer <> operators will be convenient.

    >> I don't want to use char in WHERE clause to make it sargable
    >Can you elaborate please?

    When I use string data type, I thought I may end up using SUBSTRING or DATEDIFF functions on these column.

    >Why are you recreating the SQL Agent wheel?
    That's a good question, and I asked it myself but I could not find a more elegant solution yet.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What does your custom scheduling process do that SQL Agent does not?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2013
    Posts
    4
    There will be 1000s of schedules. SQL Agent solution will be too complicated for such load.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •