How do I flush the PRINT buffer in TSQL

Question

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

User · Answer

Yes    The first parameter of the RAISERROR function needs an NVARCHAR variable   So try the following    -- Replace PRINT function DECLARE  strMsg NVARCHAR 100  SELECT  strMsg    Here  s your message     RAISERROR   strMsg  0  1  WITH NOWAIT   OR   RAISERROR  n Here  s your message      0  1  WITH NOWAIT

User · Answer

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

User · Answer

Yes    The first parameter of the RAISERROR function needs an NVARCHAR variable   So try the following    -- Replace PRINT function DECLARE  strMsg NVARCHAR 100  SELECT  strMsg    Here  s your message     RAISERROR   strMsg  0  1  WITH NOWAIT   OR   RAISERROR  n Here  s your message      0  1  WITH NOWAIT

User · Answer

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 RAISERROR N    0  1  WITH NOWAIT   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

User · Answer

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

User · Answer

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  go   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

User · Answer

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 RAISERROR N    0  1  WITH NOWAIT   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  go   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

[sql-server] How do I flush the PRINT buffer in TSQL?

Examples related to sql-server

Examples related to tsql

Examples related to printing