Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to get Sql Database Email confirmation status

Posted on 2010-08-24
3
Medium Priority
?
903 Views
Last Modified: 2013-11-30
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 ?
0
Comment
Question by:RETAILREALM
  • 2
3 Comments
 
LVL 6

Expert Comment

by:robbe
ID: 33508588
You can use:

EXECUTE msdb.dbo.sysmail_help_queue_sp ;
GO

more info on:
http://technet.microsoft.com/en-us/library/ms187400.aspx
0
 

Author Comment

by:RETAILREALM
ID: 33508856
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
 

Accepted Solution

by:
RETAILREALM earned 0 total points
ID: 33509589
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question