October 22, 2010 at 7:09 am
When you send an email, and it fails, where do you dig in to find the specific error that was returned by the mail server to help point you to fixing the issue?
boring details below....
I'm playing around with sending text messages via SSMS database mail, and wanted to change the formatting a bit so it's more obvious my cell phone sent the message, instead my account alias "Scripts"
I've already set up db mail correctly, and i can send regular emails, or emails to the ssms email gateway with no problem.
however, as i play around and start changing some parameters, like trying to change the @from_address, sometimes the email fails...i see it in the failed items
(select * FROM [msdb].[dbo].[sysmail_faileditems]), but i don't see the reason for the failure...server rejected the from address, or whatever the specific issue was.
anyway an SMS message from email from the gateway
comes thru to the phone looking like this:
FRM:Scripts
SUBJ:Text message
MSG: OK is the non-Gmail messages working?
FROM:1410000003
08:14am 10/22/10
the code used, which is very simple, is this:
if exists(SELECT * FROM msdb.dbo.sysmail_profile where name IN('mydomain as scripts'))
BEGIN
declare @body1 varchar(150) --small for SMS
set @body1 = Left('OK is the non-Gmail messages working?', 150)
EXEC msdb.dbo.sp_send_dbmail
@profile_name='mydomain as scripts',
@recipients='9540000000@txt.att.net',
@subject = 'Text message',
@body = @body1,
@body_format = 'TEXT'
END
Lowell
October 22, 2010 at 7:35 am
ok, post coffee, with a clearer head, i found the answer.
the table sysmail_event_log, has the error description and the mailitem_id, so you can join it to failed items and see the issue:
select
err.[description] ,
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].dbo.sysmail_faileditems fail
On err.mailitem_id = fail.mailitem_id
Lowell
October 22, 2010 at 8:08 am
- How to resend email in Database Mail? http://www.sqlservercentral.com/Forums/Topic673403-146-1.aspx#bm793953
and off course also my troubleshooting script:
use msdb
go
/* is dbmail config activated ? */
Select *
from sys.configurations
where [name] = 'Database Mail XPs'
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
/* show failed stuff */
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 dbo.sysmail_faileditems AS SF
JOIN dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id
/*
********** cleanup old messages **********
Declare @sent_before datetime
Select @sent_before = dateadd(MM,1,dateadd(dd,datediff(dd,0,getdate()),0))
print convert(varchar(26),@sent_before,121)
exec sysmail_delete_mailitems_sp @sent_before = @sent_before
, [ @sent_status = ] 'sent_status' ]
delete from sysmail_event_log where log_date < @sent_before
*/
/*
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]
*/
/*
/********** cleanup old messages *********/
Declare @sent_before datetime
Select @sent_before = dateadd(MM,-1,dateadd(dd,datediff(dd,0,getdate()),0))
print convert(varchar(26),@sent_before,121)
exec msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @sent_before
delete
from dbo.sysmail_event_log
where log_date < @sent_before;
*/
Select convert(xml,' Troubleshooting Database Mail on Windows Server 2008 and 2008 R2 : Also have a look at http://sqlblog.com/blogs/john_paul_cook/archive/2010/07/04/troubleshooting-database-mail-on-windows-server-2008-and-2008-r2.aspx') as Remark
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 22, 2010 at 8:25 am
Thank you for the script and the link to the other thread;
I especially liked the snippet for resending failed mails that WayneS posted; good to have that in the toolbox.
Lowell
October 22, 2010 at 11:01 am
Indeed ... that's a very nice toolbox item :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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply