While working as Web Developer, I had a requirement to make a Windows Service act as a scheduler for processing the orders based on the status using a stored procedure. All the processing was done within the stored procedure, and basically the Windows Service would call the stored procedure at the scheduled time and report the errors occurred in the stored procedure in the following ways.

1. Log Errors in Windows Event Log
2. Log Errors in the Custom Error Log with the Error Description
3. Sent Email notifying the Error and the Error Description.

Later I was told to use a SQL Batch Job that would execute a stored procedure at the scheduled time.

All the above requirements were possible in SQL Server 2000 except getting the Error Description Message. In SQL Server 2000 there is no provision to extract the error description. We get only the error number from a statement. This feature is available in SQL Server 2005, but in my company I had to do it in SQL Server 2000.

Soon I found two things which helped me out.

1. SQL Server 2000 allows to log the errors in the SQL Server Error Log using the sp_altermessage system stored procedures. We can make a particular error to be logged by using the following statement

sp_altermessage Error No, 'WITH_LOG', 'TRUE'

2. SQL Server 2000 allows to read the SQL Server Error Log using the system stored procedure

xp_readerrorlog

So using the above two things I created a Stored Procedure (sp_GetErrorDesc), which provides the Error Description based on the Error Number. The current logic in such that it gives the latest entry of a particular error in the stored procedure. I extract the error description based on the Error Number. If multiple entries are there, I pull out the latest one based on date and time.

The following example explains how you can use this stored procedure to get the Error Description based on the Error Number.

I am using the Northwind Database and performing an update on the Customers Table.

UPDATE Customers SET CustomerID = NULL
WHERE CompanyName = 'Antonio Moreno Taquería'
PRINT 'ErrorNo:' + CAST(@@ERROR AS VARCHAR)

I get the following Error when I execute the above query. Since the CustomerID Column does not allow NULLS. You will Notice that I am Printing the Error Number.

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CustomerID', table 'Northwind.dbo.Customers'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

(0 row(s) affected)
ErrorNo:515

The Error Number is 515. Hence I will now add this ErrorNumber to the SQL Server Error Log for Logging. Using the following Query.

EXEC sp_altermessage 515, 'WITH_LOG', 'TRUE'

Now I will call the sp_GetErrorDesc stored procedure and print the Error Description.

--DECLARE THE VARIABLES 

DECLARE @ERROR INT, @ERROR_DESC VARCHAR(200)
UPDATE Customers SET CustomerID = NULL
WHERE CompanyName = 'Antonio Moreno Taquería'

--GET THE ERROR NUMBER
SET @ERROR = @@ERROR

--CALL THE STORED PROCEDURE AND PASS THE ERROR NUMBER
EXEC sp_GetErrorDesc @ERROR, @ErrorDesc = @ERROR_DESC OUTPUT

--PRINT THE EXTRACTED ERROR MESSAGE RETURNED BY THE STORED PROCEDURE
PRINT @ERROR_DESC

The output is of the above query is

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CustomerID', table 'Northwind.dbo.Customers'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

(0 row(s) affected)
Error: 515, Severity: 16, State: 2
Desc: Cannot insert the value NULL into column 'CustomerID', table 'Northwind.dbo.Customers'; column does not allow nulls. UPDATE fails..
Date: 2008-11-27 12:30:33.4

You can observe the text is green is the Error Message which was extracted using the stored procedure.

To use this Stored Procedure first make sure that you allow logging of errors in the SQL Server Error Log using the sp_altermessage. For Example

exec sp_altermessage 137, 'WITH_LOG', 'TRUE'

Or else you can also automate it as I did in my case in the following way

DECLARE @output varchar(1000)
exec sp_GetErrorDesc @ErrorNo, @ErrorDesc = @output output
if @output is NULL
BEGIN
 SET @output = 'Custom Message'
 exec sp_altermessage @ErrorNo, 'WITH_LOG', 'TRUE'
END
PRINT @output

So in this way, if a new error occurs that is not already logged in the SQL Server Error Log, the first time it will be added to the SQL Server Error Log and a custom message will be displayed.

Whatever value is stored in the @output value is returned as the custom message. But the next time that error occurs, the exact error message will be displayed.

The stored procedure sp_altermessage not only logs the error in the SQL Server Error Log but also in the Windows Event Log. Refer Figure 1.0

Fig: 1.0

I have uploaded the sp_GetErrorDesc script in the Resource section. Please feel free to use it in your own applications.