SQL Job
-
I need to create a job step that checks the current time. If the current time is less than 7 am, it wait 30 minutes and checks again unless the time is 7am or greater and then goes to the next step. Please help!
-
Why not just create a schedule for the job that can only ran after 7am?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
this job (Job B)is started by another job (Job A) and the job A complets at a different time. I cant have the job B started before 7AM. thats why want a step that checks the time if it is past 7 am then run other wise keep checking every half an hour until 7 Am.
-
Sounds like something is wrong with the design there somewhere...maybe you can add a first step to the job with WAITFOR sql command? This can be problematic if there are a number of threads or the delay is long. Make sure you understand the potential issues. It would probably be ok if the delay is short.
if datepart(hh, getdate()) < 7
WAITFOR TIME '07:00'
http://msdn.microsoft.com/en-us/library/ms187331.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
You really should post your replies in your thread. That way other people who may want the same thing can benefit from the conversation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Adding this logic will check the time and if it is not 7AM, it will keep waiting until it is 7Am. How can I add a time check every 30 minute to this? and also if it is 7 or past 7 this job step successds?
-
newdba_sql (1/22/2013)
Adding this logic will check the time and if it is not 7AM, it will keep waiting until it is 7Am. How can I add a time check every 30 minute to this? and also if it is 7 or past 7 this job step successds?Not totally sure what you mean. The thread effectively goes to suspend until that time. The system checks far more frequently that every 30 minutes. Once the system clock hits 7am the process will continue. It doesn't see if the time is exactly 7am, it checks if the current hour is less than 7. If it is less than 7 it just waits until 7 and then continues. You don't need any extra checks.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (1/22/2013)
newdba_sql (1/22/2013)
Adding this logic will check the time and if it is not 7AM, it will keep waiting until it is 7Am. How can I add a time check every 30 minute to this? and also if it is 7 or past 7 this job step successds?Not totally sure what you mean. The thread effectively goes to suspend until that time. The system checks far more frequently that every 30 minutes. Once the system clock hits 7am the process will continue. It doesn't see if the time is exactly 7am, it checks if the current hour is less than 7. If it is less than 7 it just waits until 7 and then continues. You don't need any extra checks.
It could be a problem if this step occurrs at 07:30 as it will then wait until 7AM the next day.
If you want to make sure it is 7 or later you could do this.
WHILE DATEPART(HOUR,GETDATE()) < 7 -- While the current hour is less than 7?BEGIN
WAITFOR DELAY '00:30' -- Wait for 30 minutes
END;
-
Sean Pearce (1/23/2013)
Sean Lange (1/22/2013)
newdba_sql (1/22/2013)
Adding this logic will check the time and if it is not 7AM, it will keep waiting until it is 7Am. How can I add a time check every 30 minute to this? and also if it is 7 or past 7 this job step successds?Not totally sure what you mean. The thread effectively goes to suspend until that time. The system checks far more frequently that every 30 minutes. Once the system clock hits 7am the process will continue. It doesn't see if the time is exactly 7am, it checks if the current hour is less than 7. If it is less than 7 it just waits until 7 and then continues. You don't need any extra checks.
It could be a problem if this step occurrs at 07:30 as it will then wait until 7AM the next day.
If you want to make sure it is 7 or later you could do this.
WHILE DATEPART(HOUR,GETDATE()) < 7 -- While the current hour is less than 7?BEGIN
WAITFOR DELAY '00:30' -- Wait for 30 minutes
END;
If you look at what I posted it already checks to see if the current hour is < 7 and if so it just waits until 7. The only difference is I wait until the time to execute where yours has to keep checking every 30 minutes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply