FYI: SS2016 Requires .Net FW 3.5 for DBMail

This was painful enough to figure out that I figured I would share the information with the entire group.

Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard (will not install from a exe).

 Detailz …

 DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).

Essentially there was no error information available, mail items would just sit in the queue.

Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server: “The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'” ~ which, after a lot of time with The Google, led to the answer.

Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring. 2¢, J++

Tools:

This was painful enough to figure out that I figured I would share the information with the entire group.

Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard  (will not install from a exe).

Detailz …

DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).

Essentially there was no error information available, mail items would just sit in the queue.

Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server:

“The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following:  'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'”

~

Which, after a lot of time with The Google, led to the answer. Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring.

2¢, J++

Tools:  

SELECT

    CASE sent_status

        WHEN 0 THEN 'Unsent'

        WHEN 1 THEN 'Sent'

        WHEN 2 THEN 'Failed'

        WHEN 3 THEN 'Retrying'

        END AS sent_status_desc

FROM msdb..sysmail_mailitems

SELECT *

FROM   msdb..sysmail_event_log

ORDER BY log_id DESC

SELECT @@VERSION

SELECT sent_status, send_request_date a, *

FROM msdb.dbo.sysmail_mailitems

ORDER BY a DESC

SELECT *

FROM msdb.dbo.sysmail_sentitems

USE msdb

SELECT sent_status, *

FROM sysmail_allitems

SELECT is_broker_enabled

FROM sys.databases

WHERE [name] = 'msdb';

EXECUTE msdb.dbo.sysmail_help_status_sp

-- sp_configure 'Database Mail XPs'

SELECT *

FROM msdb.dbo.sysmail_event_log

ORDER BY log_date DESC

-- select @@VERSION

SELECT *

FROM   Msdb.dbo.sysmail_profile

SELECT *

FROM   Msdb.dbo.sysmail_event_log order by log_date desc

SELECT *

FROM   Msdb.dbo.sysmail_faileditems

EXEC Msdb.dbo.sysmail_help_queue_sp