Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Database Mail’ Category

What happened to that email?

Posted by sqlwiseguy on September 10, 2009

This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail.  The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server.  If the mail server accepts the message then Database Mail is done and the status is set to sent.  So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know.  In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
SEL.event_type,
SEL.log_date,
SEL.description,
SF.mailitem_id,
SF.recipients,
SF.copy_recipients,
SF.blind_copy_recipients,
SF.subject,
SF.body,
SF.sent_status,
SF.sent_date
FROM
msdb.dbo.sysmail_faileditems AS SF JOIN
msdb.dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.

Advertisements

Posted in Code, Database Mail, SQL Server, SqlServerCentral | Leave a Comment »

Mystery of Failing Database Mail

Posted by sqlwiseguy on July 21, 2009

Have you ever seen this error when you have setup database mail:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using [Account] ([TimeSent]). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it

I have, and so have a few others based on my experience in the forums.  If you are like me, you immediately contacted you mail server administrator to find out why the mail server is refusing your mail.  Then once, they told you that there was nothing on the mail server that should be causing this, you tried using GMAIL/Hotmail and got the same error.  Now what do you do?  Fortunately for me I had a production server setup and working so decided to investigate differences.  Lo and behold, I found antivirus software installed on my desktop, but not on the server (let’s save that discussion for another time) So I looked in the antivirus log and, sure enough, mail being sent by database was being blocked by the “prevent mass-mailing worms” rule.  So I disabled the rule, and, viola!, mail sent successfully.  Of course the corporate antivirus policy almost immediately re-enabled the rule, but I had an answer.  I contacted my corporate antivirus administrator and had them add DatabaseMail90.exe (SQL Server 2005) to the exceptions list.

So if database mail is not working, check your antivirus settings.

Posted in Database Mail, SQL Server | Leave a Comment »

Ghost of SQL Server 2005 Database Mail Reply To – Fix

Posted by sqlwiseguy on July 22, 2008

If you have been following my posts at all, you know I have had some issues with Database Mail (1, 2, 3, 4). Well the first one has been fixed, I have not tested the fix yet, by Microsoft in Cumulative Update Package 8 for Service Pack 2. The article for that is here, http://support.microsoft.com/kb/953841/en-us. Microsoft never updated my Connect post, where they said it would not be fixed unless a QFE was requested, but I’m not complaining.

Now I need to try it out on my personal SQL Server.

Posted in Bugs, Database Mail, SQL Server | Leave a Comment »

Another Undocumented Database Mail "Feature"

Posted by sqlwiseguy on April 17, 2008

I was setting up Database Mail on a new server, we’ll call it Jack1, the other day and, as usual, I could not remember the name of our SMTP server. Having setup Database Mail on other servers, I decided to take advantage of the new modularity of SSMS and open up Database Mail settings on another server, Jack2, so I could find my SMTP server name. I found the SMTP server name and copied over to the new account I was setting up on my Jack1 I finished setting up the account and profile and saved it. Everything should work, right? So I go to Jack1 Database Mail, right-click, and select send test mail. Error, no profiles setup on server! What!? I just finished setting up the Profile. So I right-click and select Configure Database Mail, and lo and behold, there IS NOT a profile setup. I checked the Jack2 server and there I found the new Profile! So it appears that SSMS saves the mail profile to the last server on which you had opened Database Mail setup. I will do a little more testing and post any new findings I have.

Posted in Database Mail, SQL Server | 1 Comment »

Ghost of SQL Server 2005 Database Mail Reply To, Resolution?

Posted by sqlwiseguy on April 7, 2008

Microsoft has confirmed this as a bug which has been fixed for SQL Server 2008. If I, or you, want it fixed for SQL Server 2005 a QFE needs to be submitted.

I’m not sure I like the fact that I need to go another step to get this corrected. Fortunately I was not using this feature successfully and then had it stop after applying updates. If I had been using it I would be very upset that the fix released for 2008 was not made compatible with 2005.

What do you think?

Posted in Bugs, Database Mail, SQL Server | Leave a Comment »

Update on the Ghost of Database Mail

Posted by sqlwiseguy on March 28, 2008

After having posted the problem on SQL Server Central and getting feedback (forum discussion found here). It has been determined that the Reply To phantom is caused by applying .NET Framework 2.0 SP1. I have submitted the problem to Microsoft through Microsoft Connect. Go here if you have experienced it and want to add your voice as well.

As an aside, if you are an IT professional and do not participate in your fields on-line forums you should. You learn a lot and can be a help to others in your profession.

Posted in Bugs, Database Mail, SQL Server | Leave a Comment »

The Ghost of SQL Server 2005 Database Mail

Posted by sqlwiseguy on March 20, 2008

If you didn’t know, there is a ghost in SQL Server 2005 Database Mail. The ghost’s name is Reply To. I saw the ghost this week as I was setting up database mail on my new SQL Server. I set up a mail profile and then, as I moved on to create an account for the profile. I set up the from, the from name, the smtp server, and there is was, Reply To! I thought to myself, this is a great feature as I don’t want any replies going to the server account. I can have them sent to me! So I confidently approached the Reply To ghost and entered my email address. I successfully sent a Test Mail from the server to myself. Now, to test the repy to settings, I hit reply in Outlook! The email was sent and, NOTHING happened! I did not receive the reply, off it went to the From account. I double-checked, then triple-checked my database mail account setup, Yes, everything was set up correctly. I fearlessly checked msdb.dbo.sysmail_account and, yes, replyto_address, was there! Then I checked sp_send_dbmail and there was not a reply to parameter. Now I had found the reason for the ghost. You have nothing to fear from this ghost. Just don’t expect it to be helpful.

Hopefully this ghost will be banished from SQL Server 2008

Posted in Database Mail, SQL Server | Leave a Comment »