auto refresh a query result (F5) in SSMS

Question

auto refresh a query result (F5) in SSMS

lan2022nguyen

SSC-Addicted

Points: 459
More actions
April 14, 2023 at 4:26 pm

#4174104

i have a job that load data into a table and a separate table that keep tracks of errors (when there's issues with the data loading).
i have a query select this error table and display the columns as needed. instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the data within the same query result window?
April 14, 2023 at 7:18 pm

This was removed by the editor as SPAM
April 14, 2023 at 7:20 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

frederico_fonseca SSCoach Points: 16148 More actions · Answer 1

you can - combination of your query, plus a waitfor, plus a GO xxxx where xxx is the number of times you wish the queries before it to execute

or a while loop - potentially with a check to see if it still needs to run the query.

that together with output to text should keep you the latest errors visible on screen

This reply was modified 1 year, 6 months ago by frederico_fonseca.

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 2

something like this:

waitfor delay '00:00:03' --pause for 3 seconds

GO 3 -- run 3 times

this will result in 3 separate result pane. i'd like to keep it in the same pane if possible.

ScottPletcher SSC Guru Points: 101078 More actions · Answer 3

DECLARE @time_to_stop_running time
SET @time_to_stop_running = '4:30PM'

WHILE 1 = 1
BEGIN
    SELECT <column(s)>
    FROM dbo.<your_error_table>
    WHERE ...
    WAITFOR DELAY '00:02:00'
    IF CAST(GETDATE() AS time) >= @time_to_stop_running
        BREAK;

END /*WHILE*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16148 More actions · Answer 4

lan2022nguyen wrote:

something like this:
waitfor delay '00:00:03' --pause for 3 seconds GO 3 -- run 3 times
this will result in 3 separate result pane. i'd like to keep it in the same pane if possible.

the "go xxx will give you output immediately - while loop will not unless you use a "RAISERROR('You are reading article on SQL PRINT statement', 0, 1) WITH NOWAIT;" in between loops

in either case if you output to grid you will always get multiple recordset grids which makes it unreadable

This reply was modified 1 year, 6 months ago by frederico_fonseca.

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 5

would be nice for result of the loop will be in the same recordset grid, but doesn't appear so.

thanks everyone!

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

lan2022nguyen wrote:

instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the data within the same query result window?

I have to ask... why do you need to do this? Depending on the answer, there may be a better way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 7

Jeff Moden wrote:

lan2022nguyen wrote:
instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the data within the same query result window?
I have to ask... why do you need to do this? Depending on the answer, there may be a better way.

curious to know if there's a way to automate my clicking of the F5 to refresh the query result on the same recordset grid.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 8

lan2022nguyen wrote:

Jeff Moden wrote:
lan2022nguyen wrote:
instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the data within the same query result window?
I have to ask... why do you need to do this? Depending on the answer, there may be a better way.
curious to know if there's a way to automate my clicking of the F5 to refresh the query result on the same recordset grid.

Understood that from the beginning but why are you doing so to begin with? What are you looking for in the result set, when, and why so often?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 9

Jeff Moden wrote:

lan2022nguyen wrote:
Jeff Moden wrote:
lan2022nguyen wrote:
instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the data within the same query result window?
I have to ask... why do you need to do this? Depending on the answer, there may be a better way.
curious to know if there's a way to automate my clicking of the F5 to refresh the query result on the same recordset grid.
Understood that from the beginning but why are you doing so to begin with? What are you looking for in the result set, when, and why so often?

basically checking for errors often. wanted to know when there's issue with loading data from external source into a table. if error, i need to take action. so frequency is key. hope this answer the question.

i think i'm happy to send the result to text. this is acceptable.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

lan2022nguyen wrote:

Jeff Moden wrote:
lan2022nguyen wrote:
Jeff Moden wrote:
lan2022nguyen wrote:
instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the data within the same query result window?
I have to ask... why do you need to do this? Depending on the answer, there may be a better way.
curious to know if there's a way to automate my clicking of the F5 to refresh the query result on the same recordset grid.
Understood that from the beginning but why are you doing so to begin with? What are you looking for in the result set, when, and why so often?
basically checking for errors often. wanted to know when there's issue with loading data from external source into a table. if error, i need to take action. so frequency is key. hope this answer the question.
i think i'm happy to send the result to text. this is acceptable.

Ah... now we're getting somewhere. Thanks for the reply.

To make a suggestion, frequency isn't actually the key. The actual occurrence of an error is the key. You're already putting errors into a table. Why not put a trigger on that table that will fire off an email to you when there's an error and set up a rule to cause a screen popup saying that an error email has been received? The email could contain the results of the query you're currently hitting the f5 button for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

I am very curious as to why you want to build a manual process to monitor for errors. There are many automated ways to notify when an error occurs and forcing someone to manually monitor the results of a query isn't a good solution.

As @JeffModen pointed out - you could use a trigger. Or - you can create an agent job that runs every minute checking for errors and sending a notification when found, or you can modify the load process so it traps the error and notifies when encountered, or any number of other automated methods.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 12

thanks for suggestions @jeff Moden, @Jeffrey Williams. i'll take a look. am new to the whole DBA thing and learning as i go.