asked on

Error Trapping for xp_sendmail

I'm using VB6 against an SQL Server database.
A few of our user mailings use the extended sp:
xp_sendmail.

Can anyone tell me-
what are my options for error trapping on xp_sendmail?
I know if there is an error, it is written to the SQL Server log.
But, I'd like to know if there is a way to trap any errors such as: mail server down, etc.
Right now, on the VB side, I have this generic error routine; but this won't detect any xp_sendmail errors:
---------------
ErrorMessage:
If Cn.Errors.Count > 0 Then
For Each objerror In Cn.Errors
Message = Message & objerror.Number & ":" & objerror.Description _
& "(" & objerror.NativeError & ")" & vbCrLf & vbCrLf
Next
MsgBox Message, vbCritical, "Stored Procedure Error"
Else
If Err.Number <> 0 Then
Message = ""
Message = Message & Error & "(" & Err.Number & ")"
MsgBox Message, vbCritical, "VB Error"
End If
End If
---------------

Thanks in advance
John

ASKER CERTIFIED SOLUTION

nigelrivett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jtrapat1

ASKER

Thanks for the post.
I did what you suggested by raising an error after xp_sendmail is called from my own sp.
This is good; an error is generated and can be read from my VB program if the mail server fails.
Can you just explain a little more of what you meant by the last part:
:By putting the requests into a table and having a task scheduled to make the xp_sendmail calls, etc.
Thanks
-------
Here's what I did:

While not @@fetch_status = -1
BEGIN
select 'sendassignments' = @curbillnum
select @sendlast = 'Y'
if @prevanalyst <> @curanalyst
BEGIN
select 'sendassignments' = @curanalyst
select @sendto = '=' + @prevanalyst
exec master..xp_sendmail @recipients = @sendto, @copy_recipients = @fromuser, @subject = 'Bill Assignments',
@message = @curmsg, @set_user = 'administrator'
IF (@@error <> 0 )
BEGIN
RAISERROR ('An Error Occurred In xp_sendmail',10,1)
END
/*if return <> 0 then select 'sendassignments' = return */
select @curmsg = 'The following assignments were made by ' + @uname + char(13)
END

nigelrivett

Usually mail is a fragile part of the system and you don't want everything to stop because you can't send one. And you can never tell if the send is successful anyway.

If instead of sending the mail on-line you put a request into a table then you can have a task which runs every 5 mins or so and sends all the mails for which there are requests in the table. In this way if the mail server stops an alert can be sent o someone who can fix it and the backlog of mails will be sent.

Also if you decide that you don't want to use xp_sendmail (because you have other systems using a better solution) then it is easy to change because the send is not dependent on any application.
Also if you find that the send is causning problems on the server you can just set up another server - link to this one and use the remote server to send mail.

jtrapat1

ASKER

Could you help me out with one more thing concerning this sp?
I tested on a server with the mail server stopped and my error check stepped right over it - it didn't raise an error.
(Actually, the Exchange server is not configured with SQL Server yet, - I'm on a test server.)
Would an error like this be picked up by the stored procedure?

Thanks
John

nigelrivett

You can only catch whatever xp_sendmail raises an error for but I would expect the SP to pick this up.