I’ve written a stored procedure that refreshes the data of any test and development environment from our production environment; the stored procedure runs under a login which has sufficient permissions to complete the task so that any user with execute permissions on the stored procedure can refresh their databases from the production databases. One of the issues I encountered in developing this was displaying to the user running the stored procedure all of the errors returned by the restore statement; this is because when the SQL Server error handling system functions, such as ERROR_NUMBER(), ERROR_MESSAGE(), etc are used to retrieve and display the errors to the user running the stored procedure, only the last error is displayed to the user.
As an example if a user tried to restore a database using a stored procedure such as the one created in OriginalRestoreProc.sql but there was not enough available space to complete the restore; the only error displayed to the user is:
Error 3013: Restore was terminated abnormally.
This means that the user has no immediate way of telling why the restore failed, and the error itself is not in any way user friendly.
In order to capture all of the errors to return to the user I set up an extended event session to capture the restore errors, please note that extended events were introduced in SQL Server 2008 onwards, so this technique will not work in SQL Server 2005 or less.
The extended events engine for SQL Server was introduced as a lightweight replacement for SQL Trace, an event session is setup to watch for a given event, and when it occurs, to capture the data configured by the event session and sending this data to a given target, for an in-depth introduction to extended events see technet article http://technet.microsoft.com/en-us/library/bb630354(v=sql.100).aspx .
Listing RestoreErrors_XEvent.sql creates the extended events session that looks for the errors generated by restore statements called by the stored procedure login account, specifically the errors listed in the table below.
To get the captured extended events returned to the user, the original stored procedure was modified, by adding additional code to the catch block; this code reads the extended event data written to the target of the extended event session which is in XML format, and then converts it to a tabular format and returns the results to the user, see listing ModifiedCatchBlock.sql, as mentioned earlier the Catch block contains the code, called when a SQL Server script encounters an error if a Try-Catch construct is used for error handling.
So using the scenario mentioned earlier where a user tries to restore a database but there is not enough space to complete the restore, the image below shows the additional results that are now available to the user via the results tab in SQL Server Management Studio. Note that in this scenario where there is not enough space to complete a database restore two errors are raised. The first error to show the reason why the restore failed and how to correct it. The second shows the phase of the restore that the error occurred in, namely the planning phase.
You will not always see two errors returned. The number of errors returned vary depending on the initial raised error. For example, if a user tried to restore a database, that their current session was connected to, only one error would be returned. That would be: Error: 3102, %ls cannot process database '%ls' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Conclusion
Using the power of Extended Events, I was able to get report all errors raised within a stored procedure and display them to a user. This is particularly useful in SQL Server 2008R2 where there are extended events but no THROW error handling command.
To read more about extended events, I recommend that you check out Jonathan Kehayias' excellent series on the subject http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/an-xevent-a-day-1-31-an-overview-of-extended-events.aspx
