Sent_status values ​​on msdb.dbo.sysmail_mailitems

Question

I am sending emails from SQL Server and should match the values ​​of the sent_status column in the msdb.dbo.sysmail_mailitems table with something more descriptive.

So far, I have identified two values:

• 1 = 'Submitted'
• 2 = 'Failed'

Are there even more possible meanings, and if so, what do they represent?

Answer 1

 sent_status, --0 new, not sent, 1 sent, 2 failure or 3 retry. 

Answer 2

In the MSDN page for the corresponding msdb.dbo.sysmail_allitems table, the description for sent_status states:

The state of the mail. Possible values:

sent - sent a letter.

unsent - mail is still trying to send a message.

retrying - by the mail of the database it was not possible to send a message, but trying to send it again.

failed - a mail letter could not send a message.

Connecting the two species together as follows:

 SELECT DISTINCT mi.sent_status, ai.sent_status FROM msdb.dbo.sysmail_allitems ai FULL OUTER JOIN msdb.dbo.sysmail_mailitems mi ON ai.mailitem_id = mi.mailitem_id 

Gives a relation that can be expressed with the following CASE expression:

 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