Post reply

Check success of sp_send_dbmail

  • November 3, 2014 at 10:47 am

    #312227

    The docco says the sp will return 'Mail queued.' if it executes successfully. I see that if I execute it in a query window, but how can a I check for that when I'm calling it from a t-sql script? e.g.

    EXEC [msdb].[dbo].[sp_send_dbmail]

    -- .........

    --.........

    IF <something> -- mail was queued

    BEGIN

    -- do tasks for successful mail

    END

  • November 3, 2014 at 11:39 am

    #1756381

    From the docs:

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

    That really means that it was able to connect to the mail server and queue the message. That doesn't mean that your email will reach it's recipient. If the recipient email was invalid or whatever, that issue will be handled by the mail server, and your sending script will never know about it.

    declare @retval int;

    exec @retval = msdb.dbo.sp_send_dbmail @profile_name = 'dbmailprofile', @recipients = 'RandomGuy@Random.Com', @subject = 'Test', @body = 'Test'

    if (@RetVal = 0)

    PRINT 'Email Sent'

    else

    PRINT 'Error sending mail: ' + @@ERROR

  • November 3, 2014 at 3:19 pm

    #1756424

    I know how to check the mail queue to see if it's gone to the mail server, I just want to check execution of the send command while processing a bunch of notifications.

    Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply