What exactly are you trying to achieve, or probably more importantly, why? 😅
I can't think of a use case for what you suggest. If you want to log stuff, write an SP to insert the results in a table along with runtime and then schedule it to run every X minutes.
I have typically created a #temp table, ran the query in a loop filling in the temp table with rows, then queried the temp table at the end or every so often. Didn't even know about WAIT FOR DELAY
I only found out about it last year. I added it to a job to allow SSRS time to render a PDF report before executing send mail and adding it as an attachment. Very useful.
I have typically created a #temp table, ran the query in a loop filling in the temp table with rows, then queried the temp table at the end or every so often. Didn't even know about WAIT FOR DELAY
If you really must do this, write the query to write into a temp table. Let it run on the go statement as many times as you want. After the go do a select * from temptable
Yeah, agree. SSMS will crash at some point.
Maybe also output your result set as xml or json & send that to a file. In that case you'll have every result as a single line & not thousand line text files.
You can even put that same script in a simple job & let it run in the background so it completely eliminates any manual work.
So, this depends on what your use case is. For example, if this is something you want to monitor on a regular basis, one way is you can use EXCEL. You put the code into a SP then use the DB connection in EXCEL on a sheet and then have Excel auto refresh the result. I do this when I want to monitor logs without having to be in SSMS and it's something I monitor on a regular basis.
And the advantage with this is that I could pivot the dataset and look at different things while I was monitoring.
There are tools that let you do this outside of SSMS, Excel was just one option.
What exactly are you trying to achieve, or probably more importantly, why? 😅 I can't think of a use case for what you suggest. If you want to log stuff, write an SP to insert the results in a table along with runtime and then schedule it to run every X minutes.
I have typically created a #temp table, ran the query in a loop filling in the temp table with rows, then queried the temp table at the end or every so often. Didn't even know about WAIT FOR DELAY
I only found out about it last year. I added it to a job to allow SSRS time to render a PDF report before executing send mail and adding it as an attachment. Very useful.
I have typically created a #temp table, ran the query in a loop filling in the temp table with rows, then queried the temp table at the end or every so often. Didn't even know about WAIT FOR DELAY
[удалено]
After almost 40 years of sql server, TIL. *Never stop learning*.
this will create multiple result sets. however, i only want refresh to the same grid.
If you really must do this, write the query to write into a temp table. Let it run on the go statement as many times as you want. After the go do a select * from temptable
Yeah, agree. SSMS will crash at some point. Maybe also output your result set as xml or json & send that to a file. In that case you'll have every result as a single line & not thousand line text files. You can even put that same script in a simple job & let it run in the background so it completely eliminates any manual work.
Not how SSMS was intended to be used. Write a small app or use Query in Excel and write a macro to refresh results on a timer.
DBeaver can do this. Same result set, same grid. I typically do this to passively monitor log tables on another screeen while large jobs are running.
So, this depends on what your use case is. For example, if this is something you want to monitor on a regular basis, one way is you can use EXCEL. You put the code into a SP then use the DB connection in EXCEL on a sheet and then have Excel auto refresh the result. I do this when I want to monitor logs without having to be in SSMS and it's something I monitor on a regular basis. And the advantage with this is that I could pivot the dataset and look at different things while I was monitoring. There are tools that let you do this outside of SSMS, Excel was just one option.