When executing long running queries on SQL server, the messages from PRINT statements do not return to the caller sometimes until the whole process is finished, or intermittently at best.
If running a long running process from the GUI or debugging in SQL server management studio, it can be desirable to let the user know where things are at and prevent them force quitting your application prematurely.
By using the following statement messages can be sent back immediately, note the NOWAIT, this forces the message back right away:
RAISERROR (N'working', 10,1) WITH NOWAIT
In the above example, 10 is the severity level of the error we are raising and 1 the state. This is not severe enough to stop the statement running.
I use this all the time now for getting a feeling of security from seeing something happening on long running scripts.
With C#/VB.NET using ADO.NET to connect to the procedure, we have a “infoMessage” event that is raised whenever a message comes back from SQL. By handling this event we can proceed to update the user GUI with that message, or another message as required.
cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
txtMessages.Text += "\n" + e.Message;