Solved

how to get Sql Database Email confirmation status

Posted on 2010-08-24
3
894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 39
SQL eating up memory? 16 39
how to make geography query faster?  SQL 7 45
Sql server Error message 3 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

756 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