Post reply

SQLServer Agent not sending dbmail on job completion

  • maxo85

    SSC Veteran

    Points: 227

    September 26, 2020 at 3:08 am

    #3791057

    Hi Everyone!

    I have a problem with a SQL Server 2017 with dbmail. I have the attached script and when I run it manually it works ok and sends the mail, and it also registers the activity on sysmail_event_log. But when I run it with a job, the step works fine but no mail is sent and no log can be found in sysmail_event_log.

     

    My SQL Server is the following:

    Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    Any ideas on how to fix this?

    Thanks!!!

    Attachments:
    You must be logged in to view attached files.

  • Site Owners

    SSC Guru

    Points: 80372

    September 27, 2020 at 3:10 am

    #3791134

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeff Moden

    SSC Guru

    Points: 997320

    September 27, 2020 at 3:16 am

    #3791136

    Dunno for sure but my first guess is... is the user for the SQL Agent Service setup as an email user on your email system?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • maxo85

    SSC Veteran

    Points: 227

    September 28, 2020 at 2:31 am

    #3791214

    Hi!

    Sql Server Agent is running with the default user as seen below.

    I tried removing the .csv attachment from the query above and the mail is sent normally... So, it seems the thing gets broken when I attach a csv file from a view!

    Captura

    No clue on how to solve it though!

     

     

  • webrunner

    SSC-Dedicated

    Points: 30400

    October 1, 2020 at 1:41 pm

    #3792247

    Hello,

    Not sure if these links can address your issue, but I found a couple of them useful while troubleshooting some email issues I ran into.

    http://www.midnightdba.com/DBARant/complete-troubleshooting-guide-for-sql-server-databasemail-dbmail/

    https://www.sqlshack.com/troubleshooting-database-mail-failures/

    https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/check-the-status-of-e-mail-messages-sent-with-database-mail?view=sql-server-ver15

    https://stackoverflow.com/questions/14649685/check-if-sp-send-dbmail-was-successful/14652916

    For this last link, this answer in particular:

    sysmail_faileditems will only get you the list of failed emails. If you need to see a list of successfull emails you need to use sysmail_mailitems.

    Use the below query to get details for all emails sent the same date:

    SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())

    And here is the complete query to get all the failed emails from the past 24 hours:

    SELECT items.subject ,

    items.recipients ,

    items.copy_recipients ,

    items.blind_copy_recipients ,

    items.last_mod_date ,

    l.description

    FROM msdb.dbo.sysmail_faileditems AS items

    LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l

    ON items.mailitem_id = l.mailitem_id

    WHERE items.last_mod_date > DATEADD(DAY, -1,GETDATE())

    HTH

    -- webrunner

    • This reply was modified 3 weeks, 2 days ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • TUellner

    SSCrazy

    Points: 2619

    October 1, 2020 at 4:43 pm

    #3792311

    Maybe the SQL Agent account cannot access the CSV file. If it's coming from a network location I believe you will need an AD account for SQL Agent. Something to check anyway.

  • Tac11

    SSCertifiable

    Points: 6802

    October 12, 2020 at 8:02 pm

    #3796470

    You can try:

    Execute as 'sa'

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply