Database Mail not working on new SQL Server 2022 instance

  • We are struggling to get Database Mail working for our new SQL Server 2022 Standard Edition instance ((RTM-CU20) (KB5059390) - 16.0.4205.1 (X64)).

    • Test emails work fine (Database Mail | Send Test E-Mail...)
    • Emails sent from a PowerShell script within a SQL Server Agent job step running on the same instance using the same SMTP server work fine.
    • Email notifications from job success/failure/completion do NOT work

    There is nothing in the Database Mail Log to indicate any errors. In fact, rows only appear in the log when test emails are sent. Nothing happens when jobs (that are configured to notify the Operator) run.

    Interestingly, the following can be seen in the job history: "NOTE: Failed to notify '<operator_name>' via email."

    Right-clicking on the Operator and selecting Properties | History shows "(Never e-mailed)" for emails. This is despite the Jobs being configured with "Always" in the E-mail column under the Notifications section.

    All of the Alert System settings under the SQL Server Agent look correct, and the test emails prove that emails can be sent fine. It is as if the Notification functionality of SQL Server Agent jobs is broken under this version of SQL Server.

    Any suggestions please?

     

    • This topic was modified 1 month, 1 week ago by zoggling.
    • This topic was modified 1 month, 1 week ago by zoggling.
    Attachments:
    You must be logged in to view attached files.
  • Did you authorise sql agent to use the mail profile?

    Use the following script

    EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
    @databasemail_profile=yourprofilename,
    @use_databasemail=1
    GO

     

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • That worked... by why? (thank you!)

    You can see from the "01.jpg" screenshot that the mail profile had already been configured under the Alert System of the SQL Server Agent properties. And we had actually run this same statement programmatically as part of our instance setup script, but running it again now seems to have fixed the issue.

    Somewhat of a mystery!

  • UPDATE: We had missed off "@use_databasemail=1" in our calling of this stored procedure. It always worked for us in the past, so perhaps @use_databasemail no longer defaults to "1" in SQL Server 2022?

  • @use_databasemail is an integer input with no default, it must be specified

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I believe this must be a bug.  I have the exact same scenario, I mean exact.  Even after turning this on via the T-SQL method listed above I still can't get mine to work.   I've never had this issue on any SQL Server box except this 2022 machine.  Anyone else have any ideas other than see if a new CU fixes it later?

  • just suggesting to check the basics...database mail has a dependency on dotnet 3.5, which is not installed in an operating system by default, and is easy to mis-remember the dependency. i have a pattern to run this PowerShell script for all new machines that get SQL Server, if that helps:

     

    ###################################################################################################
    ## .net 3.5 is part of the Net-Framework-Core and is required for database mail
    ###################################################################################################
    if((Get-CimInstance -ClassName Win32_OperatingSystem).ProductType -eq 3) #3=Server,1=Workstation
    {
    Write-Host "Setting Server Dependancy Net-Framework-Core" -ForegroundColor Green
    Install-WindowsFeature Net-Framework-Core
    }
    else
    {
    Write-Host "Setting Workstation Dependancy NetFx3" -ForegroundColor Green
    Enable-WindowsOptionalFeature -Online -FeatureName "NetFx3"
    }

     

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell wrote:

    just suggesting to check the basics...database mail has a dependency on dotnet 3.5, which is not installed in an operating system by default, and is easy to mis-remember the dependency. i have a pattern to run this PowerShell script for all new machines that get SQL Server, if that helps:

    ###################################################################################################
    ## .net 3.5 is part of the Net-Framework-Core and is required for database mail
    ###################################################################################################
    if((Get-CimInstance -ClassName Win32_OperatingSystem).ProductType -eq 3) #3=Server,1=Workstation
    {
    Write-Host "Setting Server Dependancy Net-Framework-Core" -ForegroundColor Green
    Install-WindowsFeature Net-Framework-Core
    }
    else
    {
    Write-Host "Setting Workstation Dependancy NetFx3" -ForegroundColor Green
    Enable-WindowsOptionalFeature -Online -FeatureName "NetFx3"
    }

    That was a great idea, but it didn't work for me unfortunately.   Same behavior.

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

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