Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Execute stored procedure during time period

I have a job that runs every 10 minutes on our system and executes a series of stored procedures

I have a couple (out of the 20 or so) that I need to execute ONLY between 9am and 6pm daily.

How do I do this...

I don;t want to create anothe job
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
probably the easiest option would be to create a new stored procedure which will invoke the existing one(s)

so it needs all the same parameters as the existing procedure(s) and maybe a couple of extra optional ones

put the time test into the new procedure

ie

if hour(getdate()) between 9 and 17
   or  @runflag = 'Y'      -- a new parameter to allow for testing basically or special runs...
begin
     exec @rc = yourexistingstoredprocedure @parm1,@parm2
     ...
end
else
begin
     print 'procedure xxxxx not called out of run hours 9 - 18'
end

return

you could set up a table to control the run times which may allow for easier job control/maintenance

but you are essentially duplicating what is already available via the job scheduling system...
Avatar of Larry Brister

ASKER

Thanks