SQL Server Management Send Mail as Part of a Job

Question

I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, and SQL Server Agent will also send error emails to me, so my email profile seems to be set up correctly. The code:

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = 'myaccount@emailcom',
@subject = 'Bad Data Report',
@query = N' USE Database;
   DECLARE @yesterday date = getdate() - 1;  select * from Timecards WHERE Date = @yesterday AND (DailyHours > 24.1 OR DailyHours < 0 ) ',
@exclude_query_output = 1,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_attachment_filename = 'BadDataQuery.txt'
END

Following some advice from similar issues on this site, I tried looking through sysmail_allitems and sysmail_faileditems, but neither log any attempts to send. I also created a stored procedure that I had the job call as a roundabout way to send the email, but that also did not send an email nor leave an error. Any ideas what might be going on?

Answer 1

Here are some steps to troubleshoot the situation. After executing them you're probably be able to identify what's causing your problem. Execute one of them at a time to eliminate the possible problems:

• Replace @attach_query_result_as_file = 1, for @attach_query_result_as_file = 'Danchat',. The idea is to cause an error to see if causes the job to fail or if it goes unnoticed too;
• Clean the job step and leave only a simple mail command like this: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MailProfile', @recipients = 'myaccount@emailcom', @subject = 'Test'; and try to run the job. Don't use begin/end or anything else on the step, just a simple code like that;
• To find if the problem comes from the parameter of the @query variable run the complete code of your question on the job, but use this @query = N'SELECT 1;' instead of your original query. If the email arrives, it means you only have to correct that internal query of yours, if the email is not sent, you should start removing the other options until the email is sent to find the culprit.
• Try using this: @execute_query_database = 'YourDatabase', @query = N'select * from Timecards WHERE Date >= ''20210418 00:00:00.000'' AND Date < ''20210419 00:00:00.000'' AND (DailyHours > 24.1 OR DailyHours < 0 )', assuming this interval contains any data. If not, query your table for an interval that has at least one row to see if it works properly.
• Finally, if the mail is not sent only the moment you specify a table on your query, it's possible the problem is related to privileges (even though you are using an admin account - maybe some change in context is happening). Try configuring an extended event session to capture requests and you will be able to verify that.

---

Solution:

From chat discussion we were able to figure out the problem was in fact a permission problem and he was able to capture the actual user running the internal query following my last suggestion about an XE Session.