Can't CATCH Errors from sp_send_dbmail

Question

We use sp_send_dbmail to notify us of certain problems and sometimes we attach files to the emails. Occasionally we will get an Error 32 (file in use) error. I've put some code together to send the email without the attachment in this situation but no matter what I do I'm unable to CATCH the error and ignore it.

I'd like to be able to handle or ignore any errors from sp_send_dbmail because this causes a job failure in production.

I tried implementing the technique described in this article http://www.sqlusa.com/articles2008/trycatch/ like so:

CREATE PROC dbo.sp_send_email
(
    @recipients         VARCHAR(100),
    @body               VARCHAR(max),
    @subject            VARCHAR(100),
    @file_attachments   VARCHAR(100)=''
)
AS
BEGIN
DECLARE @retcode INT

BEGIN TRY
    EXEC @retcode = msdb.dbo.sp_send_dbmail 
        @recipients=@recipients,
        @subject =@subject,
        @body= @body,
        @file_attachments=@file_attachments
    PRINT '@@ERROR: ' + CONVERT(VARCHAR, @@ERROR)
    PRINT 'Retcode ONE: ' + CONVERT(VARCHAR, @retcode)

    IF @retcode <> 0
        EXEC @retcode = msdb.dbo.sp_send_dbmail 
            @recipients=@recipients,
            @subject =@subject,
            @body= @body
END TRY

BEGIN CATCH
    SELECT ERROR_MESSAGE() "sp_send_dbmail: Error Description",ERROR_SEVERITY()"Error Severity"
END CATCH

RETURN @retcode

END
GO


CREATE PROCEDURE dbo.sp_test_send_email
AS
BEGIN
DECLARE @retcode INT

BEGIN TRY
    EXEC @retcode = dbo.sp_send_email @recipients='[email protected]', @subject='Test Mail', @body='This is a test', @file_attachments='C:\temp\stage\test.txt'
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() "sp_test_send_email: Error Description",ERROR_SEVERITY()"Error Severity"
END CATCH

RETURN @retcode
END
GO

BEGIN TRY
    DECLARE @retcode INT
    EXEC @retcode = dbo.sp_test_send_email
    PRINT 'Retcode: ' + CONVERT(VARCHAR, @retcode)
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() "test: Error Description",ERROR_SEVERITY()"Error Severity"
END CATCH

This is what gets returned:

Msg 22051, Level 16, State 1, Line 0 Failed to open attachment file 'C:\temp\stage\test.txt'. Executing API 'CreateFile' failed with error number 32. @@ERROR: 0 Retcode ONE: 1 Mail (Id: 11) queued. Retcode: 0

The email does get sent but the Msg 22051 never gets caught. This is what causes the failure in production.

This may be the way it is: http://connect.microsoft.com/SQLServer/feedback/details/687544/2008-sp1-engine-sp-send-dbmail-does-not-fall-into-a-catch-block-properly

However, if there is something I'm missing and there is a way to eat the errors from sp_send_dbmail I'd sure like to know how.

Thanks,

Ray

Answer 1

Take a look at the documentation for sp_send_dbmail here: http://msdn.microsoft.com/en-us/library/ms190307.aspx

Notice you should use the return code from sp_send_dbmail and not try/catch:

Return Code Values

---

A return code of 0 means success. Any other value means failure. The error code for the statement that failed is stored in the @@ERRROR variable.

Result Sets

---

On success, returns the message "Mail queued."