Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Get Timer Like Funcationality in SSMS to Auto execute a Query every N incremenets Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 1:53 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:11 AM
Points: 892, Visits: 1,560
Is there a way in SSMS 2008 to get a query to execute every N seconds with a Wait For X seconds so that the query waits a few seocnds before each execution?

I have a query that I have to press F5 for very 15 seconds or so and it would great if I could get the thing to auto run every X seconds for a maximum number of execiutions or for a maximum duration. I've managed to use the WIATFOR within a WHILE loop to do this but the query resulst are all shown after the Last executionj fo the loop and I'm trying to see the results as each execution of the query runs.

The pseudocode would be something liike this:

WHILE @iCOunter < 10
BEGIN

SELECT X FROM TABLE WHERE Conditions

SELECT @iCOunter = @iCOunter + 1

END

Make sense? Is this kind of thing possible in SSMS?

Thanks


Kindest Regards,

Just say No to Facebook!
Post #1378446
Posted Monday, October 29, 2012 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
you can use a neat trick with RAISERROR to get an immediate result inside the loop, if that's what you want/need.

a basic example:
--print error immediately in batch
declare @i int,
@err varchar(100)
--set @i=1
while 0=0
begin
SET @err = 'Progress So Far: Step ' + convert(varchar(30),ISNULL(@i,1)) + ' completed.'
raiserror (@err,0,1) with nowait
waitfor delay '00:00:02'
set @i=ISNULL(@i,1) + 1
end



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1378452
Posted Monday, October 29, 2012 3:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:55 PM
Points: 4,046, Visits: 9,200
Have you tried replacing the While loop with a GO N?

SELECT *
FROM MyTable

waitfor delay '00:00:02'

GO 5




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1378486
Posted Tuesday, November 13, 2012 12:09 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:11 AM
Points: 892, Visits: 1,560
I was hoping for somethingbuilt-into SSMS like a query option or similiar but I will test both suggestions made.

Thanks


Kindest Regards,

Just say No to Facebook!
Post #1384240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse