SQL Server Management Send Mail as Part of a Job

All we need is an easy explanation of the problem, so here it is.

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 = '[email protected]',
@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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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 = '[email protected]', @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.

Method 2

please verify if your database mail service is properly configured and check it with the "send test email…" and look into the log for detalis. Usually the mail server has to be configured to accept relay from your sql.

SQL Server Management Send Mail as Part of a Job

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply