Flush print statements to client (SQL Server Management Studio)

Question

Please see the code below:

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test]
AS
BEGIN
begin tran
begin try
    SET NOCOUNT ON;
    DECLARE @ID int
    DECLARE @Count int
    set @Count=0
    DECLARE Employee_Cursor CURSOR FOR
SELECT id
FROM Person3;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor INTO @ID
WHILE @@FETCH_STATUS = 0
   BEGIN    
        WAITFOR DELAY '000:00:01'
        SET @Count = @Count+1
        Print @Count        
      FETCH NEXT FROM Employee_Cursor INTO @ID;
   END;
   commit tran
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
end try
begin catch
ROLLBACK tran
end catch
END

The stored procedure runs and then the following output is flushed to the client all at once (because there are three records in: person3):

1
2
3

How do you flush the output to the client on each iteration of the cursor? I am looking for an answer that is compatible with SQL Server 2005.

I have tried usinbg the RAISEERROR statement as desribed here: How do I flush the PRINT buffer in TSQL?, however all the statements still appear at the end.

You can use GO (batch seperator) to flush messages, but since you are in a transaction (giant batch) I don't think that will work. Also, cursors and WAITFOR should be avoided in SQL if possible. — StingyJack, May 20 '13 at 18:26
Have you tried the exact same query without the try/catch block? — Tombatron, Jul 28 '13 at 15:27
With RAISERROR('foo', 10, 1) WITH NOWAIT :: the output shows up in the Messages tab in SSMS during query execution. — mobill, Nov 01 '13 at 19:39
possible duplicate of [How do I flush the PRINT buffer in TSQL?](http://stackoverflow.com/questions/306945/how-do-i-flush-the-print-buffer-in-tsql) — usr, Apr 29 '14 at 15:53

score 2 · Answer 1 · answered Aug 13 '15 at 19:58

Use the combination of a RAISERROR severity of 0 to 10 and the WITH NOWAIT clause for a statement that sends output to the Messages windows immediately.

RAISERROR ('Now that''s what I call a message!', 0, 1) WITH NOWAIT

Reference: https://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement/

Flush print statements to client (SQL Server Management Studio)

1 Answers1