Posting the results of a SP using a delayed loop in Query Analyzer

[hoialmen]

OK, here's one for you...

I have a Stored Procedure that I run which returns key information on production database blocking. Whenever we put out a new release of our software, the DBA group spends a lot of time monitoring blocking to isolate any problems with data access in the new release. Currently this process involves manually running the SP over and over again. What I would like to do is set up a time-delayed loop in Query Analyzer that continuously runs the SP every, say, 30 seconds and refreshes the results window with the new info. Currently I've got a loop that seems to run the SP approximately every 30 seconds but the problem is that the results don't post to the results window until I hit the "Cancel Query" button and then it posts the results for every time it ran during that period.

Any of you SQL wizzes ever done anything like this before? As always, suggestions will be greatly appreciated.


Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net

 

[hoialmen]

Hi again,

In addition to the content of my previous post, I would also like this SP to kill any SPIDs that meet certain criteria. Example: != to certain LoginNames(some we don't want to kill), > a specified number of blocked users, > than a specified blocking duration, etc. I guess this would just be a matter of passing these values back from the SP (which I'm essentially already doing), evaluating them and calling a KILL statement if the criteria are met.

Thanks again!


Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net

 

[vongrunt]

Suggestion: don't do it in QA - make a small application with datagrid, you'll have much greater control over user interface.