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
