6

I have an email failing with function return value of '101' and @@error value of '0' (no rows are added to sysmail_allitems).

Where can I find documentation on return codes for this function?

Example code showing how I obtain the above values:

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'OBFUSCATED',  
    @recipients = @DL,  
    @subject = 'OBFUSCATED',
    @body = @emailBody,
    @body_format='html',
    @query = @reportQuery,
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @filename,
    @query_result_separator = @temp,
    @query_result_header = 1,
    @mailitem_id = @mailitem_id
    ;

    set @temp = @@ERROR;
0

2 Answers 2

12

Error code 101 is because your query result attachment is larger than the "MaxFileSize" Database Mail configuration option.

Investigation

My first attempt at figuring this out was to run sp_helpttext on the sp_send_dbmail procedure to look at the implementation:

EXEC sp_helptext 'sp_send_dbmail';

The main body of that procedure returns a bunch of different status codes, from 0 up to 21. It doesn't mention 101. However, it calls into a few other stored procedures, like sysmail_verify_profile_sp, so I glanced in that one:

EXEC sp_helptext 'sysmail_verify_profile_sp';

But that only returns codes from 0 to 4.

Later the proc calls into sysmail_verify_addressparams_sp, let's check it out:

EXEC sp_helptext 'sysmail_verify_addressparams_sp';

Shucks, that only returns 0 or 1.

Next up is sp_RunMailQuery:

EXEC sp_helptext 'sp_RunMailQuery';

It calls sysmail_help_configure_value_sp and sp_isprohibited, both of which only return 0 or 1.

Black Box

At the end of sp_RunMailQuery, a system extended stored procedure is called: xp_sysmail_format_query.

We can't grab the source code for this, but I had a hunch from earlier in the proc:

--Get the maximum file size allowed for attachments from sysmailconfig.  
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize',   
                                            @parameter_value = @fileSizeStr OUTPUT

I noticed that you are using the attachment parameter, and the query results + max file size both get passed into the extended stored procedure as well.

Repro City

I set my Database Mail maximum file size to 10 bytes, and then ran:

DECLARE @result AS INT;

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail',  
    @recipients = '[email protected]',  
    @subject = 'This is the subject',
    @body = 'This is the body',
    @body_format='html',
    @query = 'SELECT * FROM sys.messages;',
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'big-file.csv',
    @query_result_separator = '===',
    @query_result_header = 1;

SELECT @result;

Note: the "sys.messages" table has a bunch of string data that I knew would be more than 10 bytes.

And sure enough, I got 101 as the return code:

Error 101

Bugz

As CR241 helpfully pointed out, the documentation says that @@ERROR should contain the error id number that corresponds to the appropriate error message in the sys.messages table:

The error code for the statement that failed is stored in the @@ERROR variable. - sp_send_dbmail - Return Code Values

Instead it's 0 in my testing (which you noted in your original question). This seems like a bug to me.

2
  • Postscript: It is possible to run a query in a Query Window, copy results into Excel, save the file and find a file size under 100kb, then run the same query via sp_send_dbmail and have the attachment size threshold breached - because, for example, large source columns (such as varchar(8000)) (yes, even varchar) will output all whitespace in the mailed results, whereas whitespace will be excluded when copying from the query window. Workaround: explicitly type cast columns to types of smaller size. Commented May 10, 2019 at 1:33
  • For what it's worth, today I got a function return value of 102 and @@error = 0. I had noted previously this is the error code for a syntax error in the SQL. Commented Apr 28, 2020 at 4:46
2

According to sp_send_dbmail

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 @@ERROR variable.

sp_send_dbmail() returns 0 if the process of sending mails could be started without error. This means, mails have been set in the database mail queue, and for every mail the mailitem_id for the message is returned in the result set.

Any return value other than 0 means error. The precise error code of the failed statement can be seen in @@ERROR.

After using sp_send_dbmail(), the mail is sent asynchronously and it still can fail for many reasons. So if sp_send_dbmail() returns 0, it does not mean final success.

You might look in msdb.dbo.sysmail_faileditems as in the case of error.

2
  • 1
    Thanks @CR241 - per comments on the question, however, the mail doesn't make it into sysmail_allitems, so won't be in sysmail_faileditems. Following SQL returns 0 rows. use msdb; go select top 100 * from dbo.sysmail_faileditems; If I query sysmail_allitems, there is no entry near the timestamp at which I recorded the error codes that were returned in the attempt to send mail. Commented Jan 15, 2019 at 22:06
  • Doh! Thanks. A useful reminder as the code base I am looking at normally uses 0 for failure and positive integers for success (i.e. the opposite of this sproc).
    – Zeek
    Commented Apr 15, 2021 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.