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?
Best Answer
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:
@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;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;@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.@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.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.