Getting feedback / progress from batches and stored procedures
Ever wanted to get feedback and interim results like a progress from a stored procedure ? Well, not that easy as the results such as PRINT information is send after the batch has been completed. If you want to get information back from your batches you can use the property FireInfoMessageEventOnUserErrors in conjunction with the InfoMessage event handler to get information back as soon as SQL Server is able to send something.
Using RaisError (Ever asked yourself why there is only one “e” in Raiserror ? That is from the old Sybase days where two same characters were cut back to one only :-) ) can help in that case. You will have to be aware that RaisError used with the wrong severity (second parameter) might terminate the batch or the connection, severities less or equal to 10 will just send an informational message to the calling stack.
RAISERROR (Message, Severity, State) –> e.g. RAISERROR (‘SomeMessageForYou’,0,10)
The state is for custom usage, it was meant to give a hint to the caller where the procedure / batch terminate or called the event. This will be also the way we will use this in a small example to show how we can get back information to the caller. Although the Information messages are a good way to send life signals back to the client, SQL Server will batch these informational messages also together up to the end of the batch unless you use the WITH NOTWAIT option of the RaisError.
Basically at the client, the code we are using is the following:
conn.FireInfoMessageEventOnUserErrors = true;
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
In the conn_InfoMessage method we are evaluating the information from the thrown “errors”, determine the calling batch from the Message text of the error and the progress from the state.
if (Message == "Batch1")
{
progressBar1.Value = PercentageComplete;
}
else if (Message == "Batch2")
{
progressBar2.Value = PercentageComplete;
}
As the following sample application shows (attached to the Blog post, we will be able to give the caller information back about the current state of the execution. The downside to that is for sure that you will have to implement additional coding in your batches / procedures.
-Jens