I would suggest one of the below:
Method 1
- For each error condition, create an inline, table-valued user-defined function which generates the erroneous results.
- Make a table which contains all of the function names, with a field for the email recipients.
- Build a procedure (MailErrorResults) which takes a function name as a parameter. If will retrieve the email recipients from the table and execute msdb.dbo.sp_send_dbmail, sending in
'SELECT * FROM ' + FunctionName + '();'
as the query for the attachment.
Build a procedure (MailErrorResultsAll) which uses a cursor to loop over the table with the function names. In the loop, you will execute dynamic SQL along these lines:
'IF EXISTS(SELECT * FROM ' + FunctionName + '()) BEGIN EXEC MailErrorResults @FunctionName = ' + @FunctionName + '; END'
(Beware of SQL injection attacks possible by adjusting the FunctionName values in the table.)
You could omit building the separate procedure MailErrorResults, but then you are writing the entire msdb.dbo.sp_send_dbmail call in dynamic SQL, which is ugly and error prone.
You could avoid MailErrorResults hitting the function table again by sending it everything it needs (e.g. the recipients), but your dynamic SQL in MailErrorResultsAll would become more complicated, and the function table will likely be too small for this to matter. This is especially true if you end up having more information in the function table, such as a field describing the problem along with information on how to fix it.
The advantages of this method are that (1) the SQL is only written once; (2) the error detection SQL can be written using validation (no dynamic SQL quotation mark issues); (3) a reusable execution plan can be generated; and (4) eventually the check can be embedded into your application logic. The disadvantage is that the query is run twice for each check-- to see if there are errors and then during the error sending process. That disadvantage can be mitigated with Method 2.
Method 2
You would essentially reproduce Method 1, but you could eliminate the double call to the function by creating a permanent table to store the error results. Your dynamic SQL would look something like this:
'INSERT INTO ErrorList (ErrorRunNumber, a, b, c) SELECT ' + CONVERT(varchar(50),@ErrorRunNumber) + ', a, b, c FROM ' + @FunctionName + '();'
You would have a separate table called ErrorRuns which would store each execution of this process for a particular function. You wouldn't need two procedures. Your single procedure would loop over the function table, running the above dynamic SQL for each function (getting a separate run number for each, of course), and then your msdb.dbo.sp_send_dbmail call would be passed a query like this:
'SELECT a, b, c FROM ErrorList WHERE ErrorRunNumber = ' + CONVERT(varchar(50),@ErrorRunNumber) + ';'
This trades storage space and extra writes for the double reads of the existence check in Method 1. This could be superior if your error detection is resource intensive.
Method 3
One other option, which I don't recommend, is to simply put the raw SQL statements into a functions table. You could then pass them into dynamic SQL without worrying about the single quote issues, because they could be retrieved from the table as native strings. I would only explore this if you had an aversion to user-defined functions for some reason.
For example, if your end users were really writing the queries themselves and did not have user-defined function creation rights, you might have to explore this.
Another twist to this method would be to have the procedure responsible for saving the user-specific SQL execute dynamic SQL to create the user-defined function, and then use one of the methods above.
The easier you make it for end users to execute their own SQL in your environment, though, the more security concerns you will have.
Best Answer
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 thesp_send_dbmail
procedure to look at the implementation: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:But that only returns codes from 0 to 4.
Later the proc calls into
sysmail_verify_addressparams_sp
, let's check it out:Shucks, that only returns 0 or 1.
Next up is
sp_RunMailQuery
:It calls
sysmail_help_configure_value_sp
andsp_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:
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:
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:
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:Instead it's 0 in my testing (which you noted in your original question). This seems like a bug to me.