Capturing an error from xp_sendmail
-
The scenario is as follows:
I have a stored procedure that sends out 10 mails through xp_sendmail.
As people come and go in my workplace, I get the occassional error where the xp_sendmail will fail with the error cannot resolve the recipient. Subsequently, the stored procedure exits and does not try to xp_sendmail the next mail.
I want to be able to
1. Find a way of capturing an error.
2. Enable the sp to continue on to the next xp_sendmail.
The first is the priority. I have been using @@error and it seems to return 0 sometimes and other numbers at other times. @@trancount is always 0. I tried to insert the output into a temp table, but a null value is returned so i cannot do that, either.
Any help would be appreciated. if someone has covered both my aims, all the better.
------------------------------
The Users are always right - when I'm not wrong! -
xp_sendmail returns one of two values (from BOL)
Return Code Values
0 (success) or 1 (failure)
This is how you should check how the proc succeeded with it's intended work.
Just checking @@error after exec xp_sendmail will only tell you if it executed, not necessarily what it actually managed to do.
declare @err int
set @err = 1 -- just a simple init
... do stuff before sending the mail
exec @err = xp_sendmail <parameters>
if ( @err <> 0 ) goto erhandler
... do stuff to send the next mail or decide you're done sending
errhandler:
.. handle errors or handle how to skip the current mail and continue
if there are more to send
Hope it helps?
As a side note, xp_smtp_sendmail - found at http://www.sqldev.net/xp/xpsmtp.htm - may be worth looking at as a replacement to the 'native' MAPI-dependant xp_sendmail. It works superbly smooth.
/Kenneth
-
Thanks a bunch Kenneth.
I was making the mistake of trying to put the output into a temp table rather than a variable.
I am now execute the code in a seperate sp and returning 0 on failure and 1 on success.
An added benfit is that this does not impact on my main procedure with each xp_sendmail either, so it will continue onto the next one in the list.
------------------------------
The Users are always right - when I'm not wrong! -
Yeah, it seems a lot of folks confuses @@error values with return values from procedures.
Here's a small demo showing (what I think) you experienced...
create proc #test
as
-- do some stuff then exit with 'error'
return 1
go
declare @err int, @error int
exec @err = #test
select @err as 'returnValue', @@error as '@@errorValue'
drop proc #test
go
returnValue @@errorValue
----------- ------------
1 0
(1 row(s) affected)
Notice that depending on where we look, we get both an error and a success.
The trick lies in knowing which is which and having a clear mind about exactly what kind of error the errordetection is looking for.
/Kenneth
-
Thanks again for the advice.
I've never needed to put error handling in before, but I think I've sussed it now.
------------------------------
The Users are always right - when I'm not wrong! -
So you're saying you have never written any program code before..?
...you always (almost) need to have some errorhandling in place for (almost) every code/program you write.
/Kenneth
-
I didn't mean that literally.
I have never come across the need to check for errors in stored procedures. I work in data warehousing and perform checks on data after load etc and looking at success of jobs rather than individual sps as we have over 10k of them.
------------------------------
The Users are always right - when I'm not wrong! -
I was just kidding anyway
..so.. #10k procs huh..?
..am not going to ask 'why so many'...
..though, if anyone of them does anything that involves writing to tables, you really should have errorchecking in there...
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply