T-SQL Output Message During execution in SSMS

Question

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?

Answer 1

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

Answer 2

I believe that the prints get buffered, releasing "chunks" as the buffer fills up.

try using raiserror:

How do I flush the PRINT buffer in TSQL?

Answer 3

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

Answer 4

current suggestions don't work for SSMS 18 and above. Print then raiserror(N'', 0, 1) seems to get around this.