Database mail and query results

Interesting thread from the internal dbtalk alias that is used for supporting SQL Server technical questions.

The thread that triggered this post references the following forum problem;

https://www.unixadmintalk.com/f46/sp_send_dbmail-wont-send-email-using-query-175160/

Summary of the problem is that when you try and send mail in a transaction that uses queries then it appears to hang, if you remove the transaction all is well, so what's going on? Well BOL gives us a pretty big hint;

sp_send_dbmail (Transact-SQL)

[ @query = ] ' query '

Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max) , and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

https://msdn2.microsoft.com/en-us/library/ms190307.aspx

As it transpires the use of the separate session to run the query can cause WAY more problems than the local variable problem.

David Browne a Technology Architect out of Dallas hits the nail on the head (probably<g>):

The query is issued immediately (not queued), and in a separate session. If the query must wait to acquire locks held by the session calling sp_send_dbmail then the query execution will block, and you will experience a deadlock between the session running the query and the session waiting for sp_send_dbmail to finish. Since the deadlock involves one lock wait and a non-lock wait, however, it is not detected as a deadlock and the two sessions simply hang.

Either commit the data so the other session can see it or change the query to read the uncommitted data with (nolock).