SYSK 252: Running SQL statements at a desired time


Say, you need to run a certain stored proc or some T-SQL commands, but you must do it “after normal hours”…  You could sit and wait until the time you are allowed to hit that F5 (run) key in Query Analyzer, or you could create and schedule a SQL Agent job…  But if it’s a onetime thing, you could also use the WAITFOR TIME statement like follows:


WAITFOR TIME ’19:00:00′


BEGIN


— Your code goes here


END


 


You could even send yourself an e-mail with the results (success or failure), so you don’t have to VPN from home and check on the progress from time to time…


 

Comments (3)

  1. Adam says:

    Erk! There’s a scheduler built into SQL server? Isn’t that a bit redundant? Is there a way to run the proc/script from the system scheduler/"at" instead?

  2. Chris Romp says:

    Adam: Sometimes it’s not worth setting up a full SQL job for a one-time process.

    But if you’re asking about using the "at" command instead, the answer is yes.  Check out osql.exe for command-line SQL execution (either using an inline query or from a separate .sql file).  You could create a .cmd file which you could schedule that command for a specific time using "at."

  3. Chris Romp says:

    WAITFOR DELAY is also useful if you want a second part of your query to execute some time after the first part.

    Reference: http://msdn2.microsoft.com/en-us/library/ms187331.aspx