Title: Retrieve Error Message
Description: Retrieve the full error message for SQL Server 2000
Author: Paulo Santos
eMail: pjondevelopment@gmail.com
Environment: SQL Server 2000
Keywords: Error Message, SQL 2000

Retrieve Error Message

DISCLAIMER

The Software is provided "AS IS", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and non-infringement. in no event shall the authors or copyright holders be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the software.

Introduction

Recently I was given the task at work to migrate the invoice data from the old database to the new one due to the new ERP software the company I work for is installing, and I was requested to do it solely with T-SQL procedures.

Due the nature of the data and the ERP software the invoices need to be imported one at a time, and I need to keep a log of any invoice that, for some reason, could not be migrated.

The company currently uses SQL Server 2000, and as such it has poorly error management in T-SQL, at best we can use the @@error global variable.

Searching the net I found a website that offered some way to retrieve the complete error message, and I simply dig into the problem a little bit further, because on my tests the method on that site didn't quite worked.

The method consists in dumping the current output buffer, using DBCC OUTPUTBUFFER(@@spid), and search through the buffer to find the error message.

The procedure that I found on that website, used a fixed approach, and broke when searching for the message outside the point where it thought it should be.

Using that procedure as an inspiration I devised a new way to retrieve the error message.

I must warn that this method of acquiring the error message uses an UNDOCUMENTED resource, and thus if you use it, you are on your own. One other thing, the use of DBCC OUTPUTBUFFER requires admin level permission, this way, I can't stress enough that you do not use this code in production environment.

Take the following test code:

raiserror ('TEST 01', 16, 1)
raiserror ('TEST 02', 17, 2)
raiserror ('TEST 03', 18, 3)
go

dbcc outputbuffer(@@spid)

On my SQL Server the buffer has the following data: 

00000000   04 01 00 c8 00 37 01 00 aa 30 00 50 c3 00 00 01
00000010   10 07 00 54 00 45 00 53 00 54 00 20 00 30 00 31
00000020   00 0a 45 00 4e 00 54 00 45 00 52 00 50 00 52 00
00000030   49 00 53 00 45 00 00 01 00 00 00 fd 03 00 f6 00
00000040   00 00 00 00 00 00 00 00 aa 30 00 50 c3 00 00 02
00000050   11 07 00 54 00 45 00 53 00 54 00 20 00 30 00 32
00000060   00 0a 45 00 4e 00 54 00 45 00 52 00 50 00 52 00
00000070   49 00 53 00 45 00 00 02 00 00 00 fd 03 00 f6 00
00000080   00 00 00 00 00 00 00 00 aa 30 00 50 c3 00 00 03
00000090   12 07 00 54 00 45 00 53 00 54 00 20 00 30 00 33
000000a0   00 0a 45 00 4e 00 54 00 45 00 52 00 50 00 52 00
000000b0   49 00 53 00 45 00 00 03 00 00 00 fd 02 00 f6 00
000000c0   00 00 00 00 00 00 00 00 30 00 20 00 36 00 64 00

Examining the buffer I figured that an error message starts after an 0xAA byte (in red), after it has the following structure:

  • Size of the entire error message (2 bytes)
  • Error Number (4 bytes)
  • Error State (1 byte)
  • Error Level (1 byte)
  • Size of the text, in character, of the error message (2 bytes)
  • Variable length of bytes with the Unicode error message
  • Size of the name of SQL Server instance (1 byte)
  • Variable length of bytes with the Unicode name of the SQL Server instance
  • Size of the name of the stored procedure where the error occurred (1 byte)
  • Variable length of bytes with the Unicode name stored procedure
  • Line where the error occurred (2 bytes)

All multi-byte numbers are in little-endian formatting.

The procedure in the file above returns a table with the following information:

  • Error Number
  • Error State
  • Error Level
  • Error Message
  • Instance Name
  • Procedure Name
  • Line

Please, download the code and try it out.