Retrieve error text from extended stored proc

From: Lee Schipper (las_at_nospamlabapps.com)
Date: 02/26/04 

Date: Thu, 26 Feb 2004 01:23:58 GMT

SQL Server 2000.

I am calling an extended stored procedure that returns an error code (0 or
1) and error text on failure. On failure I would like to record the error
into a log, but I cannot figure out how to retrieve the text into a stored
procedure variable

The extended stored proc is xp_smtp_sendmail (available from
http://www.sqldev.net). The documentation states:

        The procedure returns only two return codes, 0 (zero)
        indicating successful execution, 1 indicating failure
        always accompanied with an error message.

When I call the stored proc from Query Analyzer and force an error, the
output screen shows appropriate error text, such as
    Error: connecting to server Junk

When I run the following code in a stored proc
  DECLARE @rc integer, @ec integer
  EXEC @rc = xp_smtp_sendmail etc....
  SELECT @ec = @@ERROR
  SELECT @rc, @ec

The grid output shows @rc = 1, and @ec = 0, and the messages output shows
the appropriate error text.

Anyone know how I can get the error text into a variable?

Related (possibly the same) question: How do I retrieve the text from
RAISERROR when the raised error does not use a registered error number?

TIA!
        Lee

Relevant Pages