Capturing the Error Description in a stored procedure

How do you accomplish error handling in a stored procedure? 99.99 % of people will answer use @@ERROR. How do you capture actual error message of the error raised by SQL Server within a stored procedure? Such as:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.
The statement has been terminated.

Again 99.99 % of people will answer not possible in TSQL. (or wait for Yukon/SQL 2005)

But,

What do you do if you really want this error message to be available for your systems? Suppose you want to log this error message in to your own log tables, what do you do?

Well, I cam across this situation in one of my recent projects. The requirement was to have a stored procedure running as a batch job. This procedure would collect and transform a large amount of data in a cursor. Results of transformations for each cursor fetch were to be logged into a SQL Table. In case of any errors during the transformation was also required to be logged. The cursor, the transformations, the logic and the data volume were not a big problem. The biggest question was how do I capture Error Description of error raised inside a stored procedure and assign it to a variable.

Some of other SQL professionals also have put up this question at various sites on the internet. On one of the site, somebody put up an idea and a script using DBCC OUTPUTBUFFER. Logic was to read DBCC OUTPUTBUFFER and then cleans it up for readability. However this process is slightly unreliable as even PRINT messages fills DBCC OUTPUTBUFFER.

Second Idea that came to my mind was to use sp_Oa procedures and use dynamic SQL along with SQL-DMO to capture error messages. This was a good idea but It would have made entire SP logic far to complicated.

Third idea was to use strings stored in sysmessages & replace the placeholders. This idea also got dropped very quickly.

So what do I do? While exploring sysmessages table, I remembered sp_altermessage stored procedure. The stored procedure allows DBA to configure the errors to be logged to “SQL Server Error Log”. This suddenly gave me an idea, Configure Most Likely Errors to be logged and then read them of the Error Log. I configured Errors 515, 547, 2601, 2627, 3902 with sp_altermessage to be self logging. So every time there is a Constraint Viuolation (PK, FK, NOT NULL, UNIQUE), the error was logged inside SQL Error log. Like this:

2004-04-05 12:39:37.68 spid61 Error: 515, Severity: 16, State: 2
2004-04-05 12:39:37.68 spid61 Cannot insert the value NULL into column 'PkId',
table 'TESTDB.dbo.testtable'; column does not allow nulls. INSERT fails..

I wrote a stored procedure, which requires an number (Error number captured with @@ERRROR). It reads the SQL Error log and finds out the last error with the error number passed for current spid. A Quick SQL programming allows me to select Error Description lines and join it and send it back as an output parameter.

I EXECUTED FOLLOWING

Set xact_abort off  -- required !
Declare @intError INT
Declare @VcDescr VARCHAR(1000)
Insert testtable (PkId ) select null  -- expected error as above
Select @intError =  @@ERROR
If @intError <> 0
 Begin
        Exec GetErrorStringSP @intError, @VcDescr OUTPUT
        Print @VcDescr
 End

And bingo, it worked.

You can download the code here.

Now some downsides of this approach:

1. I needed to turn on SERVER WIDE settings to turn logging on, which might be slightly troublesome for SQL. Also it increases the SQL Error log size quickly. I increased the total number of error logs for the system to 60 and put a job to cycle SQL Error logs daily midnight.
2. I needed to give rights to a non sa login to be able to execute xp_readerrorlog.
3. A technical problem that I purposely kept open in my SP was to select last error that might have occurred. Consider this situation, for SPID=50, Error = 515 has occurred and logging has been turned off after that error has occurred. After 2 hours also this error would continue to be in the SQL Error Log. But no new error descriptions will appear in SQL Error log (it is off now). Then this SP will still throw the error description same as previous one. I could well have made it to look for errors in last 10 seconds. But it was not required in my case.

This effort has seen my project requirements through. I hope it will be useful for many more of SQL professionals.