how to get Sql Database Email confirmation status

Hi Experts,

I have created a scheduled job, which sends emails using msdb.dbo.sp_send_dbmail.
I can successfully send emails, now I want to find whether that email is been sent sucessfully or not, and it is delived.
I can get a sent_status from msdb.dbo.sysmail_mailItems table, but I want to have the exact delivery status.

Is any way I can find this ?
RETAILREALMAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RETAILREALMConnect With a Mentor Author Commented:
ok, I found it

http://technet.microsoft.com/en-us/library/ms191278(SQL.90).aspx

which sayss
Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems views of the msdb database. The Database Mail external program logs activity and displays the log through the Windows Application Event Log and the sysmail_event_log view in the msdb database. To check the status of an e-mail message, run a query against this view. E-mail messages have one of four possible statuses: sent, unsent, retrying, and failed.


And its perfect answer.


 
0
 
robbeCommented:
You can use:

EXECUTE msdb.dbo.sysmail_help_queue_sp ;
GO

more info on:
http://technet.microsoft.com/en-us/library/ms187400.aspx
0
 
RETAILREALMAuthor Commented:
Hope I am not missing something there,
but can you specify how I can get that , in 10 emails these 9 are sent , 1 is in queue, 8 are successfully delivered and 1 is failed.

I cant see any clue by using above store procedure.

Thanks, please correct me if I am wrong.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.