By: Andy Novick | Comments (11) | Related: > 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 NOWAITDon'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
goRAISERROR 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:
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
