Problem
T-SQL scripts and stored procedures don’t produce their PRINT statements and RAISERROR output in a way that keeps you informed of the code’s progress. You usually have to wait until the procedure is complete before seeing messages.  How can I get insight into the code's progress?

Solution
The solution is to use the WITH NOWAIT clause of the RAISERROR statement.  It may be surprising but using RAISERROR doesn’t require that there is an error condition.  If the severity level given to RAISERROR is 0 through 10 SQL Server treats the RAISERROR as a plain message and not an error at all.  Execution continues with the next statement, even if there is a TRY/CATCH block or if SET XACT_ABORT is ON.  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, like this one:

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

Don’t be deceived if messages are obscured by the Results windows of SSMS or the Grids windows in Query Analyzer. When query results are being sent to a grid, these panes are shown as soon as command execution begins and the Messages window is hidden unless there are no results. There are two ways to address this. One option is to send the results to text using either the menu or CTRL+T.  The other option is to allow  results to go to the grid and click on the messages window or use the SSMS menu command Window/Next Pane, which by default is tied to the F6 key or Shift+F6 in Query Analyzer.

Once you've sent Results to Text with CTRL+T try this script:

DECLARE @time char(8)
PRINT '1 PRINT before anything else      ' 
                + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2 RAISERROR before WITHOUT NOWAIT %s', 0, 1, @time) 
WAITFOR DELAY '00:00:05'
PRINT '3 PRINT after the first delay     ' 
                 + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('4 RAISERROR with NOWAIT           %s', 0, 1, @time) 
        WITH NOWAIT
WAITFOR DELAY '00:00:10'
PRINT '5 PRINT after the second delay    ' 
                     + convert (varchar(30), getdate(), 8)

What you’ll see is something like this result:

1 PRINT before anything else      15:47:14
2 RAISERROR before WITHOUT NOWAIT 15:47:14
3 PRINT after the first delay     15:47:19
4 RAISERROR with NOWAIT           15:47:19

These results show up in 5 seconds.  Neither the PRINT statements on lines 1 and 3 nor the RAISERROR on line 2 show up before the RAISERROR WITH NOWAIT on line 4 is executed. Then after another 10 seconds you’ll see:

5 PRINT after the second delay    15:47:29

As you can see from the time stamps, lines 1 and 2 were executed at 15:47:14 then there was a 5 second delay until 15:47:19 when lines 3 and 4 are executed and appear.  It's the NOWAIT clause on the RAISERROR that produces line 4 that forces lines 1 through 4 to the message window.

One way to make the NOWAIT clause convenient is to write it into a simple stored procedure and I use this one frequently:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[ns_log_nowait]  
   @Msg nvarchar(2047)
/* Send a message to the caller so that it's available 
* immediately.
*
* example
print 'before the call'
exec dbo.ns_log_nowait 'This is the immediate message'
waitfor delay '00:00:10'
print 'This is after a 10 second delay'
compare this to
print 'before the call'
raiserror ('this is the msg', 0, 1)
waitfor delay '00:00:10'
print 'This is after a 10 second delay'
***********************************************************/
AS
    RAISERROR (@Msg, 0, 1) WITH NOWAIT 
go 
GRANT EXECUTE on [dbo].[ns_log_nowait] to PUBLIC
go

RAISERROR with severity under 11 isn’t treated as an error in a TRY/CATCH construct and NOWAIT has no effect on this behavior.  To see that RAISERROR with severity 0 is treated like a print statement try this script in SSMS:

DECLARE @time char(8)
BEGIN TRY
    PRINT '1 PRINT in the TRY block ' 
            + convert (varchar(30), getdate(), 8)
    SET @time= convert (varchar(30), getdate(), 8)
    RAISERROR ('2 RAISERROR with NOWAIT %s', 0 --<< Severity 0
               , 1, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:05'
END TRY 
BEGIN CATCH
    PRINT '3 PRINT in the CATCH block ' 
         + convert (varchar(30), getdate(), 8)
    PRINT '3A Error Number = ' +convert (VARCHAR, ERROR_NUMBER())
        + ' Severity = ' + convert (varchar, ERROR_SEVERITY())
        + ' Msg = ''' + ERROR_MESSAGE() + ''''
END CATCH 
PRINT '4 PRINT after the CATCH block ' 
+ convert (varchar(30), getdate(), 8)

The output from this script is shown here:

1 PRINT in the TRY block      16:03:04
2 RAISERROR with NOWAIT       16:03:04
4 PRINT after the CATCH block 16:03:09

Notice that lines 3 and 3A, in the CATCH block aren't reached even though a RAISERROR was executed.  However, if you change the severity on the RAISERROR to 11, the statement is treated as an error.  Here’s the script again with only the severity of the RAISERROR changed:

DECLARE @time char(8)
BEGIN TRY
 
   PRINT '1 PRINT in the TRY block      ' 
                + convert (varchar(30), getdate(), 8)
   SET @time= convert (varchar(30), getdate(), 8)
   RAISERROR ('2 RAISERROR with NOWAIT       %s', 11 --<< Severity 11
           , 1, @time) WITH NOWAIT
   WAITFOR DELAY '00:00:05'
END TRY 
BEGIN CATCH
   PRINT '3 PRINT in the CATCH block    ' 
                + convert (varchar(30), getdate(), 8)
   PRINT '3A Error Number = ' +convert (VARCHAR, ERROR_NUMBER())
         + ' Severity = ' + convert (varchar, ERROR_SEVERITY())
         + ' Msg = ''' + ERROR_MESSAGE() + ''''
END CATCH 
PRINT '4 PRINT after the CATCH block ' 
                + convert (varchar(30), getdate(), 8)

Which produces this output:

1 PRINT in the TRY block      16:11:13
3 PRINT in the CATCH block    16:11:13
3A Error Number = 50000 Severity = 11 Msg = '2 RAISERROR with NOWAIT  16:11:13'
4 PRINT after the CATCH block 16:11:13

Notice that the RAISERROR statement is handled by the TRY/CATCH and doesn't produce message output on its own.  Instead the error is handled by the CATCH block, which has code that prints the message on line 3A along with the severity.

If you move around between versions of SQL Server you’ll be pleased to know that NOWAIT works in SQL Server 2000, 2005, and 2008. The behavior seems to be the same in all versions of SQL Server.

Next Steps

• The next time you’re working on a long running script or stored procedure be sure to use the NOWAIT option to force progress messages to the messages window.  You might just add a few RAISERROR  WITH NOWAIT statements to the procedure or use the ns_log_nowait procedure.
• Check out these related tips on MSSQLTips.com:
  • Getting started with SQL Server stored procedures
  • SQL Server 2005 Try and Catch Exception Handling
  • Standardized SQL Server Error Handling and Centralized Logging

• Standardized SQL Server Error Handling and Centralized Logging
• SQL Server 2005 Try and Catch Exception Handling
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback