Re: Retrieve error text from extended stored proc
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/26/04
- Next message: Tibor Karaszi: "Re: Trigger question, using OR with UPDATE"
- Previous message: Robert: "on delete, on update"
- In reply to: Lee Schipper: "Retrieve error text from extended stored proc"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Feb 2004 10:57:48 +0100
Lee,
Below returns both a return code (0 or 1) and an error number. However, that
was only on first execution, the following executions, error number was 0.
I'm not sure why that it.
DECLARE @rc int
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyEmail@MyDomain.com',
@TO = N'MyFriend@HisDomain.com'
SELECT @rc, @@ERROR
Error: connecting to server smarthost
----------- -----------
1 134
However, the documentation for xp_smtp_sendmail does not state anything
about returned error number, so that isn't anything we should rely on
anyhow. Considering giving feedback on www.SQLDev.Net for such a request.
Checking panned functionality enhancements on the web-site, you will find
that returning error text as an out parameter is already a planned feature.
The error text is returned in the same way as a PRINT, i.e., as text in
contrast to a result set. Technically, this is a bit different from an error
message, which carries a severity level and state. Note that QA does not
show the red text containing error number etc for severity level under 11.
Run below in QA and you will see that all three messages are returned the
same way, as text only:
DECLARE @rc int
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyEmail@MyDomain.com',
@TO = N'MyFriend@HisDomain.com'
SELECT @rc, @@ERROR
PRINT 'Hello from print'
RAISERROR('hello from raierror', 10, 1)
However, use OSQL with the -m-1 switch, and you will see that the RAISERROR
is indeed an error message:
osql /STIBDELL\FRESH /ic:\mailtest.sql /E /n /m-1
Nevetheless, neither error messages (as in RAISERROR) nor text messages (as
in PRINT and the result from xp_smtp_sendmail) can be captured at the TSQL
level. This is an often requested feature, but you might want to request it
anyhow for future releases: sqlwish@microsoft.com.
-- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver "Lee Schipper" <las@nospamlabapps.com> wrote in message news:OUb%b.7464$qc4.1909@twister.socal.rr.com... > 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 > >
- Next message: Tibor Karaszi: "Re: Trigger question, using OR with UPDATE"
- Previous message: Robert: "on delete, on update"
- In reply to: Lee Schipper: "Retrieve error text from extended stored proc"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
