Sql-server – Error using sp_send_dbmail with a @query parameter

database-mailsql serversql-server-2016

This has been asked many times, and every time I've seen, the solution is to add the @execute_query_database parameter. Well, as you can see, I have that parameter and this code:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'GMail',
@recipients = 'brady@bradykelly.net',
@subject = 'Snappy Referrals', -- add date
@body = 'Hello, DB mail!',
@execute_query_database = 'SeniorExecs',
@query = N'SELECT * from Language'

Still gives the error below as long as the @query parameter is present. When I comment out that one line, the proc call works and the mail is sent.

Failed to initialize sqlcmd library with error number -2147467259.

When I examine the DB Mail logs, in the Sql Server section, everytime I try and execute the proc, an entry like this gets added:

Unknown,Disallowing page allocations for database 'WideWorldImporters'
due to insufficient memory in the resource pool 'default'.

At this point, the only thing to say about database WideWorldImporters is that it is on the same SQL server. I encountered it nowhere setting up a mail profile and user account.

Best Answer

Going an entirely different direction than what I was thinking of in the comments, if you're running this from within a job, this blog post suggests adding the SQL Agent account to the sysadmin fixed server role.

I doubt you have to go that far to fix this, and I suspect you only really need to grant the SQL Agent user (or whatever user owns the job in question) a minimum set of permissions, which are execute permissions on the sp_send_dbmail procedure, ensuring it's a member of the DatabaseMailUser database role in the msdb database, and finally, making sure it can access the data being returned by the query you're passing to the sp_send_dbmail routine itself.

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

Related Solutions

Sql-server – sp_send_dbmail fails when mailing queries, but no error returned, query just hangs

SQL Server – Error Checking with sp_send_dbmail

Method 1

Method 2

Method 3

Related Question