September 23, 2009 at 2:49 am
Morning,
I have a number of DTS packages that use cursors to extract and manipulate data and finally use xp_sendmail to email the results to either single people or groups of people.
Occassionally I get an error and the job History advises me that that there was a problem resolving @recipient. When a number of Emails have been sent it is difficult to identify to which Email this error refers.
What i want to do is introduce some error checking so that if an error is returned then the addressee details are sent to me in another Email. I have experimented with using @@error after the call to xp_sendmail, but this returns a 0 (no error) even when I send in a false Email address via @recipient.
Anybody have a thoughts on this? As ever, thank you for yourtime and comments.
Colin
September 23, 2009 at 3:19 am
Have you looked at a return value..
DECLARE @retval int
EXEC @retval = master.dbo.xp_sendmail ........
SELECT retval = @retval
CEWII
September 23, 2009 at 3:41 am
Hi,
Yes, have tried the following and it returns 0 even though the Emai addresss is incorrect. No warning Email received.
DECLARE @Retval int
EXEC @Retval = MASTER..XP_SENDMAIL
@RECIPIENTS = 'colllin.betteley@aecom.com',
@SUBJECT = 'Error Testing',
@MESSAGE = 'Error Testing'
IF @Retval 0
BEGIN
EXEC MASTER..XP_SENDMAIL
@RECIPIENTS = 'colin.betteley@aecom.com',
@SUBJECT = 'EMAIL TEST ERROR',
@MESSAGE = 'EMAIL TEST ERROR'
END
September 23, 2009 at 11:05 am
I'm really surprised by that, I have code that does exactly that and I have received the failure mail..
CEWII
September 24, 2009 at 3:07 am
Hi,
Extended the code - which I run in QA for testing, as follows. The final SELECT shows a 0 so the error code is being returned is being returned. Does my code work when you run in QA? Thanks.
DECLARE @Retval int
SET @Retval = 1
EXEC @Retval = MASTER..XP_SENDMAIL
@RECIPIENTS = 'colllin.betteley@aecom.com',
@SUBJECT = 'Error Testing',
@MESSAGE = 'Error Testing'
IF @Retval 0
BEGIN
EXEC MASTER..XP_SENDMAIL
@RECIPIENTS = 'colin.betteley@aecom.com',
@SUBJECT = 'EMAIL TEST ERROR',
@MESSAGE = @Retval
END
SELECT @Retval
September 25, 2009 at 9:16 am
I've actually used the @return = xp_sendmail... in a sproc which reports errors, so it does work. However, I think the problem you're running into is that xp_sendmail is reporting success since it was able to pass the message off to the mail server. The mail server is what would report an invalid file and you can't trap for that. Having said that, I have access to the mailbox associated with the mapi used by xp_sendmail, and I monitor that mailbox for undeliverable messages from sysadmin. hth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply