Execute a select statement continually - for instance every 5 minutes

  • This is probably easy, but I can't find it mentioned....

    Anyway, I need to connect to our SQL estate via a remote SQLSMS connection which seems to have a shortish time-out that is irritating, as its a lengthy process signing-in again.

    I have a select script that I like to check through the day. Is there any way to avoid the time-outs by running this script repeatedly, at say a 5 minute interval to display the results on screen? Even better would be if I could also specify a finish time so that it will say not execute beyond 5pm.

    Sound like it should be do-able, right - begin/wait/loop etc??

  • easy enough to put it into a sql agent job and then have the select insert into a table. then you can just query that table whenever you would like.

    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

  • Unfortunately I live in the world of Change Management :angry: where even a fart needs the correct approval, testing and lead-time. Adding new jobs willy-nilly isn't possible.

    This all needs to be done within the syntax of T-SQL and in a query window.

  • you can loop it.

    or you could do the agent job on a local instance of sql server(if u have one) and then run the select across a linked server).

    simple example

    DECLARE @i INT = 1;

    WHILE (@i <= 60)

    BEGIN

    WAITFOR DELAY '00:00:01'

    /*Your Script*/

    SET @i = @i + 1;

    END

    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

  • Found this which seems to work:

    [font="Times New Roman"]SELECT GETDATE() --your query to run

    raiserror('',0,1) with nowait --to flush the buffer

    waitfor delay '00:00:10' --pause for 10 seconds

    GO 5 --loop 5 times[/font]

    With the only issues being-

    1) It appends to the results window - would rather it overwrote

    2) No end-time, though suspect that could easily be done, or I could use simple arithmetic to run for a fixed period.

  • If you use the looping method posted you could change the condition of the where to use some date math so it will stop running by 5pm as you requested.

    while GETDATE() < dateadd(hour, 17, dateadd(dd, datediff(dd, 0, GETDATE()), 0))

    _______________________________________________________________

    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 6 posts - 1 through 5 (of 5 total)

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