April 13, 2012 at 12:21 pm
I am using the following t-sql code to send an email prior to updating a database:
Exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfile',
@recipients = 'AllDevelopers@ourdomainname.com',
@subject = 'Test Email',
@body = 'This is a test email.'
Generally this works with individual email addresses, but this is where it gets tricky. The "Alldevelopers@ourdomainname.com" is a group email address. And within that group are 5 more groups with over 110 users combined. When I use that email in my everyday activities it works fine. But when I try to use it in t-sql, nothing gets sent. No errors, only a "Mail queued" reply from SSMS.
I'm using 2k5 and 2k8 for this by the way.
Any suggestions?
Thank you!
April 13, 2012 at 4:55 pm
I've seen mail servers reject email to distro lists unless it comes from trusted sources to prevent spammers from hitting them. Your mail was queued, but see if it was delivered. Check the error log entry from your emails:
DECLARE @days_ago_start INT
SET @days_ago_start = 5 -- search last n-days for mail errors
SELECT l.[description] AS log_description,
mi.recipients,
mi.copy_recipients,
mi.blind_copy_recipients,
mi.[subject],
mi.body,
mi.body_format,
mi.importance,
mi.sensitivity,
mi.append_query_error,
mi.send_request_date,
mi.send_request_user,
mi.sent_account_id,
CASE mi.sent_status
WHEN 0 THEN 'unsent'
WHEN 1 THEN 'sent'
WHEN 3 THEN 'retrying'
ELSE 'failed'
END AS sent_status,
mi.sent_date,
mi.last_mod_date,
mi.last_mod_user,
N'EXEC msdb.dbo.sp_send_dbmail @profile_name=''' + p.name + N''',@recipients=''' + mi.recipients + N''',@subject=''' + mi.[subject]
+ N''',@body_format=''' + mi.body_format + N''',@body=''' + mi.body + N'''' AS resend_exec
FROM msdb.dbo.sysmail_mailitems mi
JOIN msdb.dbo.sysmail_log l ON mi.mailitem_id = l.mailitem_id
JOIN msdb.dbo.sysmail_profile p ON mi.profile_id = p.profile_id
WHERE mi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE())
AND mi.sent_status NOT IN (1, 3)
ORDER BY mi.send_request_date DESC ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 29, 2012 at 11:04 am
Using your code came up with blanks...even immediately after sending a group email.
May 29, 2012 at 11:12 am
That means the mail server accepted it from Database Mail. You'll need to check with the email server admin to see why the email delivery process ended inside the mail server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 29, 2012 at 3:03 pm
Finally approached our server email admin & explained my dilemma to him. He unchecked the requires authentication box, and once the server was propagated with the information it worked fine.
Thank you!! Works great now!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply