How do I flush the PRINT buffer in TSQL?

0 votes
asked Nov 20, 2008 by erik-forbes

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.

5 Answers

0 votes
answered Nov 20, 2008 by joel-coehoorn

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:

0 votes
answered Nov 1, 2010 by tcbrazil

Yes... The first parameter of the RAISERROR function needs an NVARCHAR variable. So try the following;

-- Replace PRINT function
SELECT @strMsg = 'Here''s your message...'


RAISERROR (n'Here''s your message...', 0, 1) WITH NOWAIT
0 votes
answered Nov 23, 2015 by robert-lujo

Just for the reference, if you work in scripts (batch processing), not in stored procedure, flushing output is triggered by the GO command, e.g.

print 'test'
print 'test'

In general, my conclusion is following: output of mssql script execution, executing in SMS GUI or with sqlcmd.exe, is flushed to file, stdoutput, gui window on first GO statement or until the end of the script.

Flushing inside of stored procedure functions differently, since you can not place GO inside.

Reference: tsql Go statement

0 votes
answered Nov 18, 2016 by eric-isaacs

Another better option is to not depend on PRINT or RAISERROR and just load your "print" statements into a ##Temp table in TempDB or a permanent table in your database which will give you visibility to the data immediately via a SELECT statement from another window. This works the best for me. Using a permanent table then also serves as a log to what happened in the past. The print statements are handy for errors, but using the log table you can also determine the exact point of failure based on the last logged value for that particular execution (assuming you track the overall execution start time in your log table.)

0 votes
answered Sep 15, 2017 by mike

Building on the answer by @JoelCoehoorn, my approach is to leave all my PRINT statements in place, and simply follow them with the RAISERROR statement to cause the flush.

For example:

PRINT 'MyVariableName: ' + @MyVariableName

The advantage of this approach is that the PRINT statements can concatenate strings, whereas the RAISERROR cannot. (So either way you have the same number of lines of code, as you'd have to declare and set a variable to use in RAISERROR).

If, like me, you use AutoHotKey or SSMSBoost or an equivalent tool, you can easily set up a shortcut such as "]flush" to enter the RAISERROR line for you. This saves you time if it is the same line of code every time, i.e. does not need to be customised to hold specific text or a variable.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter