How do I flush the PRINT buffer in TSQL?

Erik_16942 0
Viewed 111981

I have a very long-running stored procedure in SQL Server 2005 that I'm trying to debug, and I'm using the 'print' command to do it. The problem is, I'm only getting the messages back from SQL Server at the very end of my sproc - I'd like to be able to flush the message buffer and see these messages immediately during the sproc's runtime, rather than at the very end.

printing tsql sql-server
6 Answers

Use the RAISERROR function:

RAISERROR( 'This message will show up right away...',0,1) WITH NOWAIT

You shouldn't completely replace all your prints with raiserror. If you have a loop or large cursor somewhere just do it once or twice per iteration or even just every several iterations.

Also: I first learned about RAISERROR at this link, which I now consider the definitive source on SQL Server Error handling and definitely worth a read:
http://www.sommarskog.se/error-handling-I.html

Joel_Coehoorn_30430

To extend Eric Isaac's answer, here is how to use the table approach correctly:

Firstly, if your sp uses a transaction, you won't be able monitor the contents of the table live, unless you use the READ UNCOMMITTED option:

SELECT *
FROM table_log WITH (READUNCOMMITTED);

or

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *
FROM table_log;

To solve rollback issues, put an increasing ID on the log table, and use this code:

SET XACT_ABORT OFF;
BEGIN TRY
  BEGIN TRANSACTION mytran;
  -- already committed logs are not affected by a potential rollback
  -- so only save logs created in this transaction
  DECLARE @max_log_id = (SELECT MAX(ID) FROM table_log);
  /*
   * do stuff, log the stuff
   */

  COMMIT TRANSACTION mytran;
END TRY
BEGIN CATCH
  DECLARE @log_table_saverollback TABLE
  (
    ID INT,
    Msg NVARCHAR(1024),
    LogTime DATETIME
  );
  
  INSERT INTO @log_table_saverollback(ID, Msg, LogTime)
  SELECT ID, Msg, LogTime
  FROM table_log
  WHERE ID > @max_log_id;

  ROLLBACK TRANSACTION mytran; -- this deletes new log entries from the log table

  SET IDENTITY_INSERT table_log ON;

  INSERT INTO table_log(ID, Msg, LogTime)
  SELECT ID, Msg, LogTime
  FROM @log_table_saverollback;

  SET IDENTITY_INSERT table_log OFF;
END CATCH

Notice these important details:

  1. SET XACT_ABORT OFF; prevents SQL Server from just shutting down the entire transaction instead of running your catch block, always include it if you use this technique.
  2. Use a @table_variable, not a #temp_table. Temp tables are also affected by rollbacks.
user_42558240
Related