April 5, 2016 at 2:38 am
i'd like to run this code block into a sp
WHILE @CNT < @LOOPS
BEGIN
Select * from check_active_sessions
SET @CNT = @CNT + 1;
WAITFOR DELAY '00:00:01'
END;
I would like to run this query over predefined interval, but smss appends the output.
There's a way to achieve this with a command ?
April 5, 2016 at 2:52 am
WHILE @CNT < @LOOPS
BEGIN
INSERT INTO #TempTableThatYouveAlreadyCreated
Select * from check_active_sessions
SET @CNT = @CNT + 1;
WAITFOR DELAY '00:00:01'
END;
SELECT * FROM #TempTableThatYouveAlreadyCreated;
John
April 5, 2016 at 6:33 am
thanks for reply, but i need something different.
my goal is to create a real time session monitor (select on check_active_sessions)
that at every loop clear the output and display the nwe one
(like press f5 on select every x seconds)
April 5, 2016 at 6:42 am
I know what you're wanting to do is simple, but SSMS output grid just isn't designed for it. Instead, consider building a simple SSRS dashboard style report for this.
https://www.mssqltips.com/sqlservertip/1361/building-a-dashboard-in-sql-server-reporting-services/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 5, 2016 at 7:09 am
Yes, or use the Activity Monitor in SSMS or one of the built-in dashboard reports.
John
April 6, 2016 at 9:00 am
i made a batch script with sqlcmd, not the best, but it do what i need 🙂
the loop part:
FOR /l %%i in (1,1,%LOOPS%) DO (
cls
time /t
date /t
echo %INSTANCE%
echo.
SET /A CNT-=1
ECHO !CNT! LOOPS
echo --------------------
sqlcmd -S %INSTANCE% -h-1 -Q "set nocount on; select left ( 'session_id' + replicate ('',15),15),left ( 'login_name' + replicate ('',20),20)...,'query';"
echo.
sqlcmd -S %INSTANCE% -h-1 -Q "set nocount on; select left ( cast(session_id as nvarchar(10)) + replicate (' ',15),15) ,left ( login_name + replicate (' ',20),20),...,query from mysessionsview order by CPU_time desc, start_time desc;"
timeout %DELAY% > nul
)
just a little visual problem when 'query' is too long
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply