Post reply

Using WAITFOR Time

  • March 12, 2012 at 5:13 pm

    #252890

    Hi All,

    For some QA of a project, I've been asked to generate some CSV files for a single day (i.e. not on a regular basis) that they would like to have run at 1:00am. Long story short, I don't really want to get up that early to VPN in and press F5 once. I know there are several options at my disposal to schedule this task, but I'm specifically curious about the WAITFOR command.

    My question is this: Can/should I do something like

    waitfor time '01:00'

    select *

    from MyTable

    and then go home for the day? Let's assume that this query will return about 200,000 records on a table which does not have heavy traffic. What are the potential drawbacks to doing this, and any nitty gritty details on how the WAITFOR actually works would be nice to hear.

    Thanks!

    Executive Junior Cowboy Developer, Esq.[/url]

  • March 12, 2012 at 5:21 pm

    #1458255

    Long story short, I don't really want to get up that early to VPN in and press F5 once.

    That's one of the great things about being a DBA, long weird hours. now to scheduling the task. if its a one off never to be run again, your solution looks like it will work. if its a one off but may be run in the future you may be better off to create an Agent job with out a recurring schedule. That way if in 2 days they want the same report you just schedule a run once for the Agent job.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • March 12, 2012 at 5:25 pm

    #1458257

    No technical drawbacks that I'm aware of or have ever encountered. The biggest problems appear the next day. Remembering that you ran it and remembering why you ran it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 12, 2012 at 5:50 pm

    #1458259

    capn.hector (3/12/2012)

    if its a one off never to be run again, your solution looks like it will work.

    It's certainly a one-off scenario. Truthfully, the only reason I've ever come up with for using waitfor time is this kind of thing. I've found a real dirth of information on this particular feature.

    Executive Junior Cowboy Developer, Esq.[/url]

  • March 12, 2012 at 10:34 pm

    #1458286

    Also, just in case you forget, why not just build a scheduled job and capture the information every day even though you don't need it for some days. You'd never forget about it that way.

    Drop the old table and use SELECT/INTO for the SQL in the scheduled job.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 13, 2012 at 4:18 am

    #1458374

    Gabe T. (3/12/2012)

    Hi All,

    For some QA of a project, I've been asked to generate some CSV files for a single day (i.e. not on a regular basis) that they would like to have run at 1:00am. Long story short, I don't really want to get up that early to VPN in and press F5 once. I know there are several options at my disposal to schedule this task, but I'm specifically curious about the WAITFOR command.

    My question is this: Can/should I do something like

    waitfor time '01:00'

    select *

    from MyTable

    and then go home for the day? Let's assume that this query will return about 200,000 records on a table which does not have heavy traffic. What are the potential drawbacks to doing this, and any nitty gritty details on how the WAITFOR actually works would be nice to hear.

    Thanks!

    You could create a scheduled job and set it to delete itself after the job has finished successfully - the option for this is under Notification.

    [font="Times New Roman"]There's no kill switch on awesome![/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply