Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Friday, September 23, 2016 10:01 AM
Points: 922, Visits: 1,654
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: Today @ 10:42 AM
Points: 14,469, Visits: 38,080
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1378452
Posted Monday, October 29, 2012 3:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 7,839, Visits: 16,932
Have you tried replacing the While loop with a GO N?

SELECT *
FROM MyTable

waitfor delay '00:00:02'

GO 5




Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
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: Friday, September 23, 2016 10:01 AM
Points: 922, Visits: 1,654
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