SQL Server Management Send Mail as Part of a Job

Posted on

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:

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'

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 :

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.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *