Execute a select statement continually - for instance every 5 minutes

Question

Execute a select statement continually - for instance every 5 minutes

rarara

SSCommitted

Points: 1538
More actions
April 22, 2014 at 7:51 am

#145011

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??

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

rarara SSCommitted Points: 1538 More actions · Answer 2

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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

rarara SSCommitted Points: 1538 More actions · Answer 4

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/