I have a simple query which loops and I want to see the PRINT messages during the execution. The query is something like this:
WHILE 1 = 1
BEGIN
WAITFOR DELAY '000:00:10'
PRINT 'here'
END
The PRINT 'here' does not output until I stop the process. However, I want to see it while it's running. Is this possible?
You can use RAISERROR with serverity 0 and the NOWAIT option
WHILE 1 = 1
BEGIN
WAITFOR DELAY '000:00:10'
RAISERROR ('here', 0, 1) WITH NOWAIT
END
I believe that the prints get buffered, releasing "chunks" as the buffer fills up.
try using raiserror:
Try this..
DECLARE @i INT = 1
WHILE ( @i <= 10)
BEGIN
--- do something
SELECT 'Completed ' + CAST(@i AS VARCHAR(50)) + ' : ' + CAST(GETDATE() AS VARCHAR(50));
SET @i = @i + 1
END
current suggestions don't work for SSMS 18 and above. Print then raiserror(N'', 0, 1) seems to get around this.
