SQL08R2 Database Mail

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL08R2 Database Mail

16 posts, Page 1 of 2

1 2 »»

SQL08R2 Database Mail

Rate Topic

Display Mode

Topic Options

Author

Message

It's the Database!!!

Posted Wednesday, January 30, 2013 11:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

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?

Post #1413731

It's the Database!!!

Posted Wednesday, January 30, 2013 12:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

well I removed all profiles and accounts, used tsql to profiles and accounts back, and tried again, the mail is still failing.

any ideas????

Post #1413746

Lowell

Posted Wednesday, January 30, 2013 1:13 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1413751

It's the Database!!!

Posted Wednesday, January 30, 2013 1:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

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;

Post #1413756

It's the Database!!!

Posted Wednesday, January 30, 2013 1:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

Also the State = INACTIVE - BOO

Post #1413758

Lowell

Posted Wednesday, January 30, 2013 1:40 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1413759

It's the Database!!!

Posted Wednesday, January 30, 2013 1:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

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...

Post #1413766

It's the Database!!!

Posted Wednesday, January 30, 2013 1:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

I think I have found something....Stay Tuned...

Post #1413767

It's the Database!!!

Posted Wednesday, January 30, 2013 2:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

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.

Post #1413768

It's the Database!!!

Posted Wednesday, January 30, 2013 3:00 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:39 AM
Points: 136, Visits: 307

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....

BigGrin

Post #1413777

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions