none
sp_send_dbmail return codes? RRS feed

  • Question

  • Anybody knows what the return code means for sp_send_dbmail?  The documentation is off track!

    http://msdn.microsoft.com/en-us/library/ms190307(SQL.90).aspx

    "Return Code Values

    0 (success) or 1 (failure)"

    When I force an error by ommiting a recipient, I receive a return code of 20 and nothing in the variable @@error.  Anybody knows if the return codes of sp_send_dbmail are documented anywhere?

    See sample code below:

    declare @returncode int

    EXEC @returncode = msdb.dbo.sp_send_dbmail
      @profile_name = 'automail',
      @recipients = '',
      @body = 'The stored procedure finished successfully.',
      @subject = 'Automated Success Message'

    select @@error, @returncode

     

     

    Monday, March 30, 2009 7:03 PM

Answers

  • These return values are not documented since the user will be getting a crisp description about the error as Leks mentioned above. 
    Since you are interested in return value, I'll provide some of the return value description, check it out.


    Return Value Error Number Description
    ============================================
    0 NA Success Code

    1 Will provide the below  Failure Code
    error number

    2 14618 Attach results must be specified

    3 14618 No output must be specified

    4 14618 No header must be specified

    5 14618 Check if query_result_separator is specifed

    6 14618 Echo error must be specified

    13 14626 @body_format can be TEXT (default) or HTML

    15 14618 Importance must be specified

    16 14622 Importance must be one of the predefined values

    17 14618 Sensitivity must be specified

    18 14623 Sensitivity must be one of predefined values

    19 14624 Message body cannot be null. Atleast one of message, subject, query,attachments must be specified.

    20 14624 Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified

    21 14625 If query is not specified, attach results and no header cannot be true.

    If this is not clear, I've written this in my Blog check @ Return Code Values Documentation for sp_send_dbmail procedure

    Vidhya Sagar. Mark as Answer if it helps!
    • Marked as answer by Antoine F Tuesday, March 31, 2009 2:16 PM
    • Edited by VidhyaSagar Wednesday, April 1, 2009 2:23 AM
    Tuesday, March 31, 2009 2:48 AM

All replies

  • Hi Anto,

    I was just working out on the query which u have given here.

    Same thing i was also wondering and i got a kind of idea in this question.

    Now lets take a scenario where am interested in only looking error number and it does mean that when i get a error number am going to get 1 as returncode and if am not getting any error number it does mean that my returncode is 0.

    Try the following command using try & catch statements.

    begin

     

    try
    declare
    @returncode int
    EXEC
    @returncode = msdb.dbo.sp_send_dbmail
    @profile_name = 'dol',
    @recipients = '',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message'
    end try
    begin
    catch
    select
    @@error as leks
    end catch

    Now my error number is 14624 when i havent given recipients, cc or bcc.


    and in the following command i have missed profile name where my error is 14607.
    begin try
    declare
    @returncode int
    EXEC
    @returncode = msdb.dbo.sp_send_dbmail
    @profile_name = '',
    @recipients = 'leksshmanan.alagappan@dol.govt.nz',
    @body
    = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message'
    end try
    begin
    catch
    select
    @@error as leks
    end catch

    and in the final query here i have given all details and so error number is NULL , message was MAIL QUEUED.

    begin

     

    try
    declare
    @returncode int
    EXEC
    @returncode = msdb.dbo.sp_send_dbmail
    @profile_name = 'dol',
    @recipients = 'leksshmanan.alagappan@dol.govt.nz',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message'
    end try
    begin
    catch
    select
    @@error as leks
    end catch

    I wouldnt say this is a complete solution but i will try to read either error number or the returncode.

    Any if in any doubts pls reply

    Thanks,
    Leks


    Monday, March 30, 2009 11:20 PM
  • These return values are not documented since the user will be getting a crisp description about the error as Leks mentioned above. 
    Since you are interested in return value, I'll provide some of the return value description, check it out.


    Return Value Error Number Description
    ============================================
    0 NA Success Code

    1 Will provide the below  Failure Code
    error number

    2 14618 Attach results must be specified

    3 14618 No output must be specified

    4 14618 No header must be specified

    5 14618 Check if query_result_separator is specifed

    6 14618 Echo error must be specified

    13 14626 @body_format can be TEXT (default) or HTML

    15 14618 Importance must be specified

    16 14622 Importance must be one of the predefined values

    17 14618 Sensitivity must be specified

    18 14623 Sensitivity must be one of predefined values

    19 14624 Message body cannot be null. Atleast one of message, subject, query,attachments must be specified.

    20 14624 Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified

    21 14625 If query is not specified, attach results and no header cannot be true.

    If this is not clear, I've written this in my Blog check @ Return Code Values Documentation for sp_send_dbmail procedure

    Vidhya Sagar. Mark as Answer if it helps!
    • Marked as answer by Antoine F Tuesday, March 31, 2009 2:16 PM
    • Edited by VidhyaSagar Wednesday, April 1, 2009 2:23 AM
    Tuesday, March 31, 2009 2:48 AM
  • Thanks to both of you.

    I already have a try catch which I use to log errors.  However, some of my emails are not sent and no error is thrown. This is why I look at return codes.

    Here is what I notice for the simple example provided earlier:
    Without try catch: error = 0, return code = 20
    with try catch: error = 14624, return code = null

    I will add an error handling after the try catch based on the return codes.

    However since the try catch seems to block the return code, I hope I will still receive the return code for those case when the catch does not get called.

    Maybe one of you already know if there is something extra to watch for.
    Tuesday, March 31, 2009 2:15 PM
  • Vidhya,

    I will import those return codes in my project.  Do you know if they are already somewhere (sys tables or something).

    For Your Information, the 0 and the 1 seems inverted in the list you provided.  I receive 0 for success using sql 2005.  If you try the code I provided earlier with a provided recipient, the email will be sent but the return code will be 0 instead of 1.

    Thanks again!

    Tuesday, March 31, 2009 5:06 PM
  • Thanks Antoine, I've edited it.
    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, April 1, 2009 2:21 AM