Share via

Behavior of WITH NOWAIT option with RAISERROR in SQL Server

  • Article

In some situations you would like to use WITH NOWAIT option in SQL Server RAISERROR statement, but somehow you usually have to wait until the procedure is complete before seeing messages. You search through MSDN links but end up reading several different articles. Here I am trying to put all the things under one roof to make you understand how it works and what different options you could make use of. RAISERROR is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. NOWAIT is a custom option for the error which sends messages immediately to the client. Ideal behavior would be to produce stored procedure and RAISERROR output in a way that keeps you informed of how the code is progressing. So, the motive here is to get an insight of the code's progress using WITH NOWAIT option.

Please note that RAISERROR doesn’t require an error to be raised explicitly. If the severity level passed to RAISERROR as a parameter is 0 - 10 SQL Server treats the RAISERROR as a plain message and will not show it as an error. Execution continues with the next statement, even if there is a TRY/CATCH block or if SET XACT_ABORT is ON. You could use one of the RAISERROR severities of 0 to 10 and the WITH NOWAIT clause for a statement that sends output immediately. Following example will show you the same:

RAISERROR ('This is a RAISERROR condition', 0, 1) WITH NOWAIT

Once you do this, you would wonder where the output is getting generated. In SQL Server SSMS and Query Analyzer, when you run a query the results are sent to a "Grid" window and the "Messages" window is hidden unless there are no results. There are two ways to address this issue:

1. One option is to allow results to go to the Grid and click on the Messages window.

2. Another option is to use the SSMS menu bar àWindowàNext Pane (F6) or Shift+F6 in Query Analyzer.

3. You could also send the results to text using menu or CTRL+T. Once you've sent Results to Text or CTRL+T.

Now run the following lines of code:

PRINT '1. This message does not display immediately'

WAITFOR DELAY '00:00:05'

RAISERROR('2. This is raised with severity 0-10.', 1, 1) WITH NOWAIT

RAISERROR ('3. This message is displayed immediately', 0, 1) WITH NOWAIT

WAITFOR DELAY '00:00:05'

PRINT '4. It''s over now'

Output

------------

1. This message does not display immediately

2. This is raised with severity 0-10.

Msg 50000, Level 1, State 1

3. This message is displayed immediately

4. It's over now

Now, do the following:

PRINT '1. This message does not display immediately'

WAITFOR DELAY '00:00:05'

RAISERROR('2. This is raised with severity >=11.', 12, 1) WITH NOWAIT

RAISERROR ('3. This message is displayed immediately', 0, 1) WITH NOWAIT

WAITFOR DELAY '00:00:05'

PRINT '4. It''s over now'

Output

------------

1. This message does not display immediately

Msg 50000, Level 12, State 1, Line 4

2. This is raised with severity >=11.

3. This message is displayed immediately

4. It's over now

Can you find the difference between the two executions of the above batch? Well, the difference is the change in the Severity of the RAISERROR statements. In the first RAISERROR statement under the first batch of statements I have given the severity as 1 whereas in the second one the severity is 12. As I said before 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. In the first output you will get the results in plain text (black color) and you will get the text given in the RAISERROR statement first and then the Msg 50000. In the second output NOWAIT has no effect on this behavior because of Severity 12 and hence you will get Msg 50000 first and then the message text. Also, the entire output of 2nd statement will be in the red color as if SQL encountered an error.

Why Msg 50000? This is because when msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs. Another thing to remember here is that when RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

1. Outside the scope of any TRY block.

2. With a severity of 10 or lower in a TRY block.

3. With a severity of 20 or higher that terminates the database connection.

If you specify a severity of 10 or lower, the RAISERROR will return a message from a TRY block without invoking the CATCH block.

Consider following lines of code:

BEGIN TRY

    -- RAISERROR with severity 0-10 will not cause execution to jump to the CATCH block.

      PRINT '1. Execution is in TRY Block with severity 0-10'

      WAITFOR DELAY '00:00:05'

     RAISERROR ('2. Error raised in TRY block.', 5, 1) with NOWAIT

      PRINT '3. Control did not go to CATCH Block'

      WAITFOR DELAY '00:00:05'

      PRINT '4. It''s over now'

END TRY

BEGIN CATCH

    -- Use RAISERROR inside the CATCH block to return error information about the original error that caused

    -- execution to jump to the CATCH block.

      WAITFOR DELAY '00:00:05'

      RAISERROR ('5. Error raised in Catch block.',5, 1)

END CATCH;

Output

--------------

1. Execution is in TRY Block with severity 0-10

2. Error raised in TRY block.

Msg 50000, Level 5, State 1

3. Control did not go to CATCH Block

4. It's over now

In the above output you will find that since the severity is between 0-10 the control did not passed to CATCH block and the effect of NOWAIT remain as it is on RAISERROR.

Now consider following lines of code:

BEGIN TRY

    -- RAISERROR with severity 11-19 will cause execution to

    -- jump to the CATCH block.

   

      PRINT '1. Execution is in TRY Block with severity >=11'

      WAITFOR DELAY '00:00:05'

      RAISERROR ('2. Error raised in TRY block.', 16, 1) with NOWAIT

      WAITFOR DELAY '00:00:05'

      PRINT '3. It''s over now'

END TRY

BEGIN CATCH

    -- Use RAISERROR inside the CATCH block to return error information about the original error that caused

    -- execution to jump to the CATCH block.

   

    RAISERROR ('4. Error raised in Catch block', 16, 1) WITH NOWAIT

      WAITFOR DELAY '00:00:05'

      PRINT '3. It''s over now'

END CATCH;

Output

---------------

1. Execution is in TRY Block with severity >=11

Msg 50000, Level 16, State 1, Line 18

4. Error raised in Catch block

3. It's over now

Here, since the severity is more than >=11 the control immediately passed from RAISERROR to CATCH block and execution was resumed inside CATCH block which eventually raises the error. Since the control immediately passed to the CATCH block you will not see any effect of NOWAIT at all with RAISERROR defined in TRY block. However, NOWAIT will have effect with RAISERROR defined in CATCH block.

This behavior is same on all editions of SQL Server so you don't have change the code again and again for different versions :)

Sumit Sarabhai
SE, Microsoft Sql Server

Reviewed By

Mukesh Nanda
TL, Microsoft Sql Server

Comments

  • Anonymous
    November 04, 2009
    I have a sproc that uses "WITH NOWAIT". It works as advertised 90% of the time. However, with very long running scripts the old delayed output behavior returns. Is there no catch-all method to get around this problem???

  • Anonymous
    November 11, 2009
    Hi Peter, Could you please share the script of the stored proc?

  • Anonymous
    August 06, 2010
    I found today that after ~500 messages, it queues up 50 at a time.  So if you have 549 messages that will be displayed and the first 500 happen in the first five seconds and it's 10 hours between 501 and 549, you won't see much useful progress information until the end.  Client side setting?  I'm using 2005 DB and 2008 EM.

  • Anonymous
    September 02, 2011
    Please I am bit confused here. So which Raiserror gets the immediate message with NOWait.. Please let me know.

  • Anonymous
    September 02, 2011
    I am bit confused here so when do we get the immediate message using NOWAIT is it with RAISERROR 0-10 or >=11. Please let me know... My email address is anithapt@yahoo.com. Awaiting for your response on this.. -Anitha

  • Anonymous
    September 06, 2011
    Anitha, NOWAIT has ‘no’ effect if the severity is >=11. But if you specify severities of 0 to 10 along with NOWAIT clause the statement sends output immediately. The difference comes in the time we raise “Msg 50000” (in this case). In the example given above you will see that with severity 0-10 the error “Msg 50000” is delayed and we log the message (This is raised with severity 0-10) before it. However, with severity 12 we wait until we raise the error and then display the message. I hope it clears your doubt. Thanks. Sumit

  • Anonymous
    December 01, 2011
    This example doesn't work on the SQL 2008 Management Studio.

  • Anonymous
    May 21, 2015
    well, this is what you should have done and I would have given you a hand with it ages ago. That is how stupid you are in nature (if applicable depending on individual and intention) and you stoped the policemen from talking to me those days so that is what you got. Now raise the error to yourself!

  • Anonymous
    October 04, 2016
    I am using MS-SQL Express (SQL_2012). In your exemple at in my system environment RAISEERROR WITH NOWAIT displays immediately. But I have a SCRIPT that uses a recursive storeproc and at @@NESTLEVEL=3 the RAISEERROR WITH NOWAIT does not display anything at all. Is this a system version bug? Can this be a normal behavior because of the recursive process?