solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Using the NOWAIT option with the SQL Server RAISERROR statement

MSSQLTips author Andy Novick By:   |   Read Comments (5)   |   Related Tips: More > Error Handling

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



Last Update: 1/7/2009


About the author
MSSQLTips author Andy Novick
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Wednesday, January 07, 2009 - 1:40:42 PM - ScottPletcher Read The Tip

Fantastic article!  Thank you.


Thursday, January 08, 2009 - 6:40:07 AM - Senthilkumar.S Read The Tip

 


Monday, January 13, 2014 - 4:15:50 PM - Stan Read The Tip

Output of PRINT or RAISERROR surrounded by square brackets, like SET @time= '['+convert (varchar(30), getdate(), 8)+']', will be suppressed in job history or in the output file of job step. However it works ok if you just exec the stored procedure.


Wednesday, February 12, 2014 - 8:16:27 AM - Roger Tranchez Read The Tip

Thanks !!!

This is ideal for troubleshooting when for some reason you can't debug T-SQL, or when you're stuck with something that takes too much time inside a stored procedure and you get confused.

Roger


Wednesday, April 02, 2014 - 8:19:17 PM - Wraith Read The Tip

Doesn't work properly after the first 500 lines of RAISERROR WITH NOWAIT messages!

Starting from the 501-th line, it will only return the output once every 50 lines. And from the 1001-th line, it will only return the output once every 100 lines...



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.