March 11, 2009 at 9:43 am
I have an email that sql server says was sent okay, but the recipient (me) never received.
How do you have db mail resend an email it thinks was sent okay?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2009 at 12:31 pm
You can re-run the query or a job that sent you this db mail.
But before you do that try to find your original email:
- Look in the Junk mail folder. We sometimes get our important emails there
- If it is not in the Junk Mail folder, trace the route of your database mail. It works with SMTP, so I would check what SMTP relay is specified in SMTP setup of Database Mail and talk to that server admin. In my experience, admins tell you if the mail successfully passed SMTP server or there was a problem. We were able to resolve a lot of issues this way.
Regards,Yelena Varsha
March 11, 2009 at 3:54 pm
The database mail is working... I've received mails both before and after the missing one.
And the emails are set up with a filter to go into a specific folder, so they are not in the junk folder.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2009 at 3:59 pm
Then just re-run the job that sends emails.
Regards,Yelena Varsha
March 11, 2009 at 4:20 pm
can you find traces of your missing in action mail in these objects ?
use msdb
go
exec sysmail_help_queue_sp -- @queue_type = 'Mail'
go
select *
-- delete
from sysmail_event_log
order by log_date desc
select *
from sysmail_mailattachments
select *
from sysmail_allitems
order by sent_date
/*
SELECT [account_id]
,[name]
,[description]
,[email_address]
,[display_name]
,[replyto_address]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_account]
SELECT [attachment_id]
,[mailitem_id]
,[filename]
,[filesize]
,[attachment]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_attachments]
SELECT [transfer_id]
,[uid]
,[filename]
,[filesize]
,[attachment]
,[create_date]
FROM [msdb].[dbo].[sysmail_attachments_transfer]
SELECT [mailitem_id]
,[profile_id]
,[recipients]
,[copy_recipients]
,[blind_copy_recipients]
,[subject]
,[body]
,[body_format]
,[importance]
,[sensitivity]
,[file_attachments]
,[attachment_encoding]
,[query]
,[execute_query_database]
,[attach_query_result_as_file]
,[query_result_header]
,[query_result_width]
,[query_result_separator]
,[exclude_query_output]
,[append_query_error]
,[send_request_date]
,[send_request_user]
,[sent_account_id]
,[sent_status]
,[sent_date]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_mailitems]
SELECT [profile_id]
,[principal_sid]
,[is_default]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_principalprofile]
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]
SELECT [profile_id]
,[account_id]
,[sequence_number]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profileaccount]
SELECT [uid]
,[text_data]
,[create_date]
FROM [msdb].[dbo].[sysmail_query_transfer]
SELECT [conversation_handle]
,[mailitem_id]
,[send_attempts]
,[last_send_attempt_date]
FROM [msdb].[dbo].[sysmail_send_retries]
SELECT [account_id]
,[servertype]
,[servername]
,[port]
,[username]
,[credential_id]
,[use_default_credentials]
,[enable_ssl]
,[flags]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_server]
SELECT [servertype]
,[is_incoming]
,[is_outgoing]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_servertype]
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2009 at 5:35 pm
ALZDBA (3/11/2009)
can you find traces of your missing in action mail in these objects ?
It's in sysmail_allitems, with a status of sent. As well as the other emails that I did get.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2009 at 7:32 pm
as a former exchange admin, you have to go to the email folks and trace the email from the smtp server to the mailbox server to see if it was really received by the email system and delivered
March 12, 2009 at 1:08 am
As SQL Noob stated, contact your mail admins, they will be able to find your mail (or in an output queue or in an received state, but maybe filtered by anti-spam, ... settings.
We've had some mails rejected because of title content (mail title was generated by the query in the send mail job)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2009 at 7:17 am
Well, 18 hours after it was sent, I finally received that email.
However, the original question remains. In all of the msdb..sysmail objects, can a sent email be set to be resent? If so, how?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 12, 2009 at 7:32 am
check out the BOL section on db mail
last year we had an issue where we had a full transaction log in a db with a table that has 300 million or so rows. it generated almost 2 million alerts of which around 1 million were sent via email and almost crashed the mail servers.
i ended up deleting the unsent messages that were still in msdb waiting to be sent. forgot how i did it, but it took me around 20 minutes of skimming BOL
March 12, 2009 at 8:34 am
I didn't test resend features for dbmail.
I don't think this feature is supported, unless you actually recompose the mail and issue another sp_send_dbmail
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 25, 2009 at 10:53 am
WayneS (3/11/2009)
I have an email that sql server says was sent okay, but the recipient (me) never received.How do you have db mail resend an email it thinks was sent okay?
Hello WayneS,
I found the answer, remembered there was a forum question, so am responding for you and all others...
I was reading through the system SP that send the email, [msdb].[dbo].[sp_send_dbmail], and came upon these lines at approx line 453. I executed them separately with a MailItem_ID, and it was resent.
-- Create the primary SSB xml maessage
SET @sendmailxml = ''
+ CONVERT(NVARCHAR(20), @mailitem_id) + N''
-- Send the send request on queue.
EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc 0
BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')
GOTO ErrorHandler;
END
So, it seems all you have to do is construct an entry for the broker, sent it in, and viola. it'll resend it.
Hope this helps, (I know it'll help me)
Mark
Mark
Just a cog in the wheel.
September 28, 2009 at 1:26 am
Works like a charm.
Thanks for the tip Starunit. :smooooth:
Keep in mind, that with every hotfix / servicepack, this needs to be rechecked (composition of the xml as well as the working of the sproc !)
But also there, copy/paste will help out :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 6, 2009 at 10:33 pm
Hello starunit,
Thanks for finding this little bit of code, and for coming back, finding this post, and posting the reply.
I've enhanced the code... the following code will resend all of today's failed messages.
declare @mailitem_id int,
@sendmailxml varchar(max),
@rc int,
@StartDate datetime,
@EndDate datetime
-- get the dates to use in the query
set @StartDate = convert(datetime, convert(char(8), GetDate(), 112))
set @EndDate = @StartDate + 1
declare cFailedMail cursor for
SELECT mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDate
open cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0 begin
-- Create the primary SSB xml maessage
SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'
-- Send the send request on queue.
EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc <> 0
BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')
END
fetch next from cFailedMail into @mailitem_id
end
close cFailedMail
deallocate cFailedMail
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 7, 2009 at 10:38 am
Hi WayneS,
Thanks for posting your wrapper: I'll keep it on file.
I've got a couple too, but mostly I just need to correct a Recipient address and resend, so normally need to deal with one or two at a time. BUT: last year our mail server was down, and I had to deal with hundreds.. whattamess!
BTW: I happened on the code by accident: I was looking for a way to set the 'ReplyTo' dynamically when sending the email. If anyone from Microsoft reads this: we need for the ReplyTo to be accessible as a parameter when sending the mail, not as it currently is - a static value on the MailAccount.
Cheers all,
Mark Starr
Mark
Just a cog in the wheel.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply