SQL08R2 Database Mail
-
I am so ready to scream, I set up DB mail and restarted the SQL Agent services but it fails to send me a test email. I have done the following;
Added the role to msdb - added user into security and gave it permissions to msdb with the role of DB Mail. Verified user was in DatabaseMailUserRole and it is.
pinged the smtp server - pinged with replies.
verified that DB mail was enabled - it is enabled.
an ideas as to what I am missing here?
MCSE SQL Server 2012\2014\2016
-
well I removed all profiles and accounts, used tsql to profiles and accounts back, and tried again, the mail is still failing.
any ideas????
MCSE SQL Server 2012\2014\2016
-
check to see fi there is a specific error coming back from the SMTP server:
it sounds like the email goes out but is never received;
it might be no relaying allowed, SMTP AUTH command required, wrong port, so many possibilitites.
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell, The message is failed, it tries to send but fails. I can ping the smtp server and send using telnet. below are the queries I ran and was able to see that mail was in fact enabled. Any other ideas? besides rebooting the server...lol
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO
select is_broker_enabled,* from sys.databases
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
--sp_addrolemember @rolename = 'DatabaseMailUserRole'
-- ,@membername = '<database user>';
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_start_sp;
/*The mail queue should have the state of RECEIVES_OCCURRING.
The status queue may vary from moment to moment.
If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp
and then starting the queue using sysmail_start_sp.
*/
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
EXEC msdb.dbo.sysmail_stop_sp
EXEC msdb.dbo.sysmail_start_sp
SELECT sent_account_id, sent_date, sent_status FROM msdb.dbo.sysmail_sentitems;
SELECT * FROM msdb.dbo.sysmail_event_log;
MCSE SQL Server 2012\2014\2016
-
Also the State = INACTIVE - BOO
MCSE SQL Server 2012\2014\2016
-
what kind of errors did you see int eh log? exact error messages help us diangnose a lot better;
if there were no messages at all,
i think you'll need to stop and start the SQL service, not just the SMTP service,
there are several threads where this seems to happen, and bouncing the server seems to clear it, but that of course doesn't identify the root cause.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is my error;
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (Date and Time of error). Exception Message: Cannot send mails to mail server. (Failure sending mail.)
I also cleared out the queue so I would not get flooded with emails but it still says inactive, which is code for, you got a bunch of emails waiting! lol
I am still digging on things that I can do before I have to reboot, I would rather not and dig to find the issue, as painful as that sounds...
MCSE SQL Server 2012\2014\2016
-
I think I have found something....Stay Tuned...
MCSE SQL Server 2012\2014\2016
-
Well that wasn't it,
Netsend had an error after restarting SQL Agent Services, but that's okay, it was a DOS executable that has been discontinued by MS from what I understand.
MCSE SQL Server 2012\2014\2016
-
I have contacted the Admin for Active DIR, I asked him to verify that the SQL IP address was able to send out to the smtp server....
to be continued tomorrow....
😀
MCSE SQL Server 2012\2014\2016
-
:-D2 Lessons learned about setting up DB Mail;
1. Always talk to the email admin to verify the IP's are open, just because you can tel net does not mean you can get out with email.
2. Always, ALWAYS, use a script to set up email, SQL just doesn't like the GUI interface, sometimes it works, sometimes it does not.
below is the script I used, I ran it each step one at a time, it worked on all my servers and I am email happy!
Also at the bottom is a troubleshooting script to help save someone from what I went through.
ENJOY! 😀
use msdb
--Step 1
declare @profile_id smallint
exec msdb.dbo.sysmail_add_profile_sp
@profile_name='Profile Name',
@description='Profile Description',
@profile_id=@profile_id output;
--verify profile, write down your profileid
exec msdb.dbo.sysmail_help_profile_sp select * from msdb.dbo.sysmail_profile
--Step 2
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name='Account Name',
@email_address='Email Address that the email is coming from',
@display_name='Email Display name',
@description='Account Description',
@account_id=@account_id output;
--write down your accountid
exec sysmail_help_account_sp
--Step 3
--to create or update email information
declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id=1, --this has to be correct, first time is 1 but if this is more than one look in ysmail_help_profile_sp to get the number.
@account_name='Account Name',
@email_address='Email address that the email is coming from',
@display_name='Display nam ein the email',
@description='Use Account description',
@replyto_address='donotreply @ your domain name.com',
@mailserver_name='SMTP Server Name',
@mailserver_type='SMTP',
@port=25, -- only change the port id gmail or the admin is using another port
@username=null,
@password=null,
@use_default_credentials=1,
@enable_ssl=0;
--Verify account
exec sysmail_help_account_sp
select * from sysmail_account exec sysmail_help_account_sp
exec sysmail_help_profile_sp exec sysmail_help_account_sp
--Step 4
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id=1, --use your profileid
@account_name='Account Name',
@sequence_number=1
--verify Profile and Account
exec sysmail_help_profileaccount_sp
--make the profile public
exec sysmail_add_principalprofile_sp
@profile_name='Profile Name',
@profile_id=1, --use profileid
@is_default=0,
@principal_name='public'
--Profile information, verify
exec sysmail_help_principalprofile_sp
--Send test email via tql, afterwards send test email through GUI
exec msdb.dbo.sp_send_dbmail
@profile_name='Profile Name',
@recipients='Email for who it is going to',
@subject='hello…test mail',
@body='hi,
TEST TEST TEST'
--See if email was sent and what status it is in
SELECT * FROM sysmail_allitems
/*
Trouble Shooting SQL DBA Mail
*/
--Checks to see if Database mail is turned on, (0,1,1,1 is on)
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO
--Checks to see if Broker is enabled
select is_broker_enabled,* from sys.databases
--anyone with sysadmin right does NOT need DatabaseMailUserRole.
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
--This does not work, go under security, pick a user that does NOT have sysadmin rights, choose msdb, choose DatabaseMailUserRole
--sp_addrolemember @rolename = 'DatabaseMailUserRole'
-- ,@membername = '<database user>';
--Gives Profile information
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
--Tells you if email is started
EXEC msdb.dbo.sysmail_help_status_sp;
--EXEC msdb.dbo.sysmail_start_sp;
/*The mail queue should have the state of RECEIVES_OCCURRING.
The status queue may vary from moment to moment.
If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp
and then starting the queue using sysmail_start_sp.
*/
--Checks state of emails, if inactive, all that means is emails are queued and they are not going anywhere
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
--This stops and start email service
EXEC msdb.dbo.sysmail_stop_sp
EXEC msdb.dbo.sysmail_start_sp
--Shows state of emails, sent, unsent, failed
SELECT sent_account_id, sent_date, sent_status FROM msdb.dbo.sysmail_sentitems;
--email event log
SELECT * FROM msdb.dbo.sysmail_event_log;
/*
deletes all mail in queue
*/
DECLARE @getdate-2 datetime
SET @getdate-2 = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @getdate-2;
GO
MCSE SQL Server 2012\2014\2016
-
I updated the scripts explainatons, I found some errors.
MCSE SQL Server 2012\2014\2016
-
Sean,
I installed on 12 different server and 2 I had to use the script. I set them all up the same way. From some reasearch I have found that sometimes the GUI does not play well. I was just providing scripts to help.
Thanks!
MCSE SQL Server 2012\2014\2016
-
May sound like a dumb idea but have you done a test email send via the database mail GUI? Does it send that way? If not go back into database mail and click on the view log. If the sql server portion sends the mail but your smtp will not allow it to send you will see this in that errorlog in about two minutes after you hit send.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply