Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Unanswered: run commands only between the hours

    Hi,

    I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.

    I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.

    How would I go about doing that?

    Thanks much for all the insight!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    do you have a scheduled Job for the Sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    12
    Yes the job is scheduled to run between 10PM and 9AM.

    The scheduled job runs a stored procedure with about 10 steps. Each step takes multiple hours to complete. The main controlling stored procedure runs for about 15 hours on average. Way past the 9AM finish time..

    I'd like to have something in the stored procedure itself that won't launch another stored procedure if it is not between 10PM and 9AM

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Each step takes multiple hours?

    What are they doing?

    Are you using Cursors?

    Can you post one example

    Sounds like you should re-engineer the process
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The simple answer would be:
    Code:
     IF DatePart(hour, GetDate()) BETWEEN 9 and 22 THEN RETURN
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Posts
    12
    DatePart was the missing link.. I ended up with the following:

    IF (DATEPART(HOUR, GETDATE()) BETWEEN 10 AND 21)
    PRINT 'EXIT';
    ELSE
    PRINT 'Continue Running';

    Thanks much for all the help!

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Let's assume the master sproc calls 10 child sprocs. Each of those child sprocs take hours to finish. Let's say that typically around the time the child sprocs 6, 7 or 8 may get started it's 9:00 AM.

    Will the first 5 child sprocs get executed each and every day, while sprocs 6, 7 and 8 only once in a while and sprocs 9 and 10 never?

    Or will you keep track of the last child sproc that was launched on day 1 by the master sproc. And when the master sproc is scheduled to run again on day 2 to let it start with the next child sproc, so all sprocs get executed eventually.

    I second Bretts advice
    Sounds like you should re-engineer the process
    Some extra RAM, indexes, getting rid of cursors, ....

    In one extreme occasion we re-engineered an application that would get started on Friday, run during the whole weekend end would end somewhere during the day on Monday. New server, more RAM, cursors replaced by set oriented operations, new data model, ... and it ran in under 5 minutes. It took us a while to accept/realise it had really done everything of the process and not just a small part of it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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