Database Mail

Question

Database Mail

edward_hall76

SSC Eights!

Points: 882
More actions
April 24, 2013 at 8:19 am

#293021

I am trying to get my database to send emails but I can not get it to work. From everything I have seen everything seems to be enabled. when I send a test email from database email I never get it and there is nothing in the database mail log. I am using gmail on port 587 like it shows to set it up. Any suggestions

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Damian-1673 SSCommitted Points: 1622 More actions · Answer 1

may be you can try some telnet commands to send an email first and see if there is any ports are blocked to access the smtp server from your server.

edward_hall76 SSC Eights! Points: 882 More actions · Answer 2

edward_hall76

SSC Eights!

Points: 882

April 24, 2013 at 9:41 am

#1609586

How is that done

Damian-1673 SSCommitted Points: 1622 More actions · Answer 3

http://kb.mediatemple.net/questions/889/Sending+or+viewing+emails+using+telnet

Lowell SSC Guru Points: 323504 More actions · Answer 4

Damian-167372 (4/24/2013)
may be you can try some telnet commands to send an email first and see if there is any ports are blocked to access the smtp server from your server.

if it was a connectivity issue, he'd have rows in the failed mail view.

/* 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 msdb.dbo.sysmail_faileditems AS SF

JOIN msdb.dbo.sysmail_event_log AS SEL

ON SF.mailitem_id = SEL.mailitem_id

there are a lot of posts here on SSC where it seems the service broker is not processing messages; and typically stopping and starting the mail service doesn't take care of it either; most of the posts find it works after a complete server restart. Sometimes it is related to the SQL startup account not having some permissions.

EXEC msdb.dbo.sysmail_stop_sp

EXEC msdb.dbo.sysmail_start_sp

SELECT * FROM msdb.dbo.sysmail_allitems;

take a look at this MSDN article with a lot of diagnostics stuff in it;

you want to see if it's in the queue waiting to go to the mail tables, or if it's already there and it's in the not sending (which could be a firewall issue)

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/01d3b552-e722-455e-aa32-abfd2edea758/

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!

edward_hall76 SSC Eights! Points: 882 More actions · Answer 5

I ran both of these codes and for the top one nothing showed up in the results and for the

EXEC msdb.dbo.sysmail_stop_sp

EXEC msdb.dbo.sysmail_start_sp

SELECT * FROM msdb.dbo.sysmail_allitems;

it shows everything that I have tried to send out and I have attached part of the information

Calibear Ten Centuries Points: 1377 More actions · Answer 6

edward_hall76 (4/24/2013)
I ran both of these codes and for the top one nothing showed up in the results and for the
EXEC msdb.dbo.sysmail_stop_sp
EXEC msdb.dbo.sysmail_start_sp
SELECT * FROM msdb.dbo.sysmail_allitems;
it shows everything that I have tried to send out and I have attached part of the information

What user does the SQL Server and SQL Server agent run under? You should be able to tell this from the Windows SCM (Services). Does these users have permissions to perform such a task on the server?

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

m.rajesh.uk Hall of Fame Points: 3653 More actions · Answer 7

m.rajesh.uk

Hall of Fame

Points: 3653

April 25, 2013 at 2:12 am

#1609810

is your service broker enabled in msdb

edward_hall76 SSC Eights! Points: 882 More actions · Answer 8

I think so i will check when i get back to work tomorrow

edward_hall76 SSC Eights! Points: 882 More actions · Answer 9

edward_hall76

SSC Eights!

Points: 882

April 26, 2013 at 10:15 am

#1610450

service broker is enabled

edward_hall76 SSC Eights! Points: 882 More actions · Answer 10

sql server is network service and server agent is local system

edward_hall76 SSC Eights! Points: 882 More actions · Answer 11

I got it fixed changes sql server to local system and now email works.

Thanks you all for your help.

tppromoter Old Hand Points: 310 More actions · Answer 12

I don't know if this helps or not. Recently I was assigned a task to configure database email in regards to reporting the results of several steps within a job. I found that even though the email log in the system database said it could not connect to server it would still send the email on the virtual environment. However on my local workstation SQL would not connect to my mail server and send the database email. As weird as it sounds I think it is related to permissions and our domain.