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?