We help IT Professionals succeed at work.

Error Trapping for xp_sendmail

jtrapat1
jtrapat1 asked
on
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
Comment
Watch Question

You won't be able to get all errors as all you can trap are errors that xp_sendmail detects and returns.

It is a bad idea to call it directly anyway as it binds you to the xp.
Better to encapsulate it with your own sp.

create procedure sp_sendmail
@recipients varchar()
..
(whatever parameters you want to allow
as

exec xp_sendmail ...
if @@error <> 0
begin
   raiserror ...
end
go

It then becomes easy to change the email solution.
From here you can put the requests into a table and have a scheduled task making the xp_sendmail calls so giving you a email history and meaning that the application doesn't crash or hangdue to mail problems.

Author

Commented:
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      


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.

Author

Commented:
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
You can only catch whatever xp_sendmail raises an error for but I would expect the SP to pick this up.