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: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
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,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #1378446
Posted Monday, October 29, 2012 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
Have you tried replacing the While loop with a GO N?

SELECT *
FROM MyTable

waitfor delay '00:00:02'

GO 5




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
I was hoping for somethingbuilt-into SSMS like a query option or similiar but I will test both suggestions made.

Thanks


Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #1384240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse