Database Mail Configuration
-
Hi,
I am setting up Database mail in SQL Server 2008 to send mail to my Gmail account. Following are the details of the DBMail profile I've created:
email id : <mymailid>@gmail.com
Display Name : Vinu
Reply email :
Server name : smtp.gmail.com
port no: 587
Check : This server requires a secure connection
Check : Basic Authentication
username : youremailid@gmail.com ( should have gmail.com)
password : <mypassword>
confirm password : <mypassword>
Then I clicked next and also made it default public profile.
Then I use the following TSQL code to confgiure and send DBMail:
--Configuring DBMail
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
--Sending Mail
USE msdb
GO
EXEC sp_send_dbmail @profile_name='ABHI_DB_Mail',
@recipients='vijayan.vinu3@gmail.com',
@subject='Test message',
@body='This is the body of the test message.
Database Mail Received Successfully.'
The message is not being sent.
When I see the mail items using the following code:
--Viewing Sys_mailitems
SELECT mailitem_id, profile_id, recipients, subject, body, sent_status, sent_date
FROM sysmail_mailitems
It always shows sent_status as '2'(Failed).
And, after this when I view the Database Mail Log, it shows me the following error:
"The mail could not be sent to the recipients because of the Mail Server failure. (Semding Mail Using Account 5(2012-05-16T10:32:55). Exception Message : Could not Connect to Mail Server.(No Such Host is known) )"
I am configuring it for the first time.
Is this the whole process of Setting up Database mail??...Am I missing something??....This is how it is done....right??....Or do I have to configure some other Services as well??
Would be really helpful, if someone could tell me how to solve this and set up the Database Mail
-
Hi Vijay,
You are using GMAIL SMTP, please use your company SMTP & test it....
Regards
Satish
-
saidapurs (5/16/2012)
Hi Vijay,You are using GMAIL SMTP, please use your company SMTP & test it....
Regards
Satish
Doesn't Gmail SMTP work??....If I am behind a Proxy...I might need to alter my Proxy Settings....right??....How do I do that to allow SQL Server to send mails??
-
Many companies will not allow outbound SMTP from a client computer unless specifically allowed at the firewall.
To test, try to telnet to port 25 of smtp.gmail.com from the server in question (e.g. telnet smtp.gmail.com 25 from a command line) - if you get a connection timed out or no response you're being blocked and will have to contact your network team.
Joe
-
Thanks for the reply Joe.
Yes, that was the problem. I was behind a Proxy which was blocking outbound SMTP.
I finally got it working.
I have another small question. Can I configure Database Mail to send me a mail whenever there is a deadlock?....A link where I can find how to do it could be really useful.
-
vinu512 (5/16/2012)
Thanks for the reply Joe.Yes, that was the problem. I was behind a Proxy which was blocking outbound SMTP.
I finally got it working.
I have another small question. Can I configure Database Mail to send me a mail whenever there is a deadlock?....A link where I can find how to do it could be really useful.
you should be able to set up an operator (withthis email address in it) and then set up an alert to watch out for error number or severity. Whenever a deadlock happens an alert is raised which fires off an email to the operator. i've never personally used it for deadlocks but I use it for policy based management.
[font="Times New Roman"]There's no kill switch on awesome![/font] -
This will point you in the right direction if you dont have the traceflags enabled on your instances
http://msdn.microsoft.com/en-us/library/ms186385.aspx
Will need to refactor the job to send the mail, or get it so that it logs to a table and then send you a mail, many different ways.
-
anthony.green (5/17/2012)
This will point you in the right direction if you dont have the traceflags enabled on your instanceshttp://msdn.microsoft.com/en-us/library/ms186385.aspx
Will need to refactor the job to send the mail, or get it so that it logs to a table and then send you a mail, many different ways.
Thanks Anthony.
-
anthony.green (5/17/2012)
This will point you in the right direction if you dont have the traceflags enabled on your instanceshttp://msdn.microsoft.com/en-us/library/ms186385.aspx
Will need to refactor the job to send the mail, or get it so that it logs to a table and then send you a mail, many different ways.
Hi Anthony, I'm kinda new to this so don't know about all the pre-requisites for accomplishing this. The link was pretty useful and I think that I might be able to accomplish it going by the way it is done in the link.
Are there any Pre-requisite settings and configurations I have to do to get this working?
Suppose, I am working on a fresh installation of SQL Server 2008, then would I be able to accomplish this task just by doing all the things mentioned in the link and re-factoring it to send the mail??...Or do I have to configure or tweak some settings as well??
-
vinu512 (5/17/2012)
Are there any Pre-requisite settings and configurations I have to do to get this working?There a pre-requisite. Go to SQL Agent properties Alert System tab and enable "Replace tokens for all job responses to alerts".
SQL Agent service restart may be needed.
I would encourage to configure a deadlock alert on any non-production server firstly. I should take less than one hour. It is not that complicated.
If you find any issues, post it here.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply