How to log a message from t-sql script?

Recently I've been working on a long running script that will be executed directly in PROD enviroment. I needed an easy way to see a progress of t-sql script execution.

I tried to use PRINT, but it doesn’t output anything until script execution is done.

You can use some log table and insert progress status messages there, but there is an easier way:

RAISERROR ('Message', 10, 1) WITH NOWAIT

RAISERROR with severity of 10 or less doesn't trigger try catch block and can be used for log purposes. Here is a little longer example:


DECLARE @i INT = 0

WHILE 1=1
BEGIN                                
      
    IF @i >= 10
                BREAK;    
    WAITFOR DELAY '00:00:01';                                    
    SET @i = @i + 1        
    RAISERROR ('%d sec running', 10, 1, @i) WITH NOWAIT
END

Results:

Raiseerror execution example

Posted on Saturday, January 26, 2013 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5