Click here to Skip to main content
15,299,032 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to send mail using sql server and also receive mail in sql server
but It gives following error

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 16 (2017-09-08T11:32:55). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: Cannot connect to SMTP server 8.8.4.4 (8.8.4.4:25), connect error 10060).
)


What I have tried:

1)
		EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Asus01',
@description = 'Sent Mail using MSDB',
@email_address = '[EDIT: Removed user name]@gmail.com',
@display_name = 'Vinayak',
@username='[EDIT: Removed user name]@gmail.com',
@password='[EDIT: Removed password]',
@mailserver_name = 'google-public-dns-b.google.com'


when Fire query
select * from sysmail_account
output is
17	Asus01	Sent Mail using MSDB	vinayak.paul2011@gmail.com	Vinayak	NULL	2017-09-08 12:31:30.853	sa


2)

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Asus01',
@description = 'Profile used to send mail'		

-- select * from sysmail_profile
this query gives following output
13	Asus01	Profile used to send mail	2017-09-08 12:41:50.767	sa



3)
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Asus01',
@account_name = 'Asus01',
@sequence_number = 1


--select * from sysmail_profileaccount
this query gives following output
13	17	1	2017-09-08 12:44:37.163	sa


4)

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Asus01',
@principal_name = 'public',
@is_default = 1 ;


5)
Send Mail
exec msdb.dbo.sp_send_dbmail @profile_name = 'Asus01', @recipients = 'paulvinayak70@gmail.com', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'


6) Fire query
select * from sysmail_allitems
		select * from 

fist in sent_status column it show
unsent

then after some time
sent_status column
display
failed
Posted
Updated 7-Sep-17 23:20pm
v2

1 solution

You have already asked this at Error while sending mail using SQL server[^].

And my answer from that question is still valid:
You have to ensure that you are using a valid SMTP server.

But the problem seems to be that you don't know what an SMTP server is:
It is a server that accepts incoming email (which requires nowadays usually to be registered / having an account). You have to use the same settings as in your email client program for the used mail account.

That is smtp.googlemail.com for Gmail and not google-public-dns-b.google.com which is a DNS server.

But the above Google mail server will not accept mails at port 25. It supports only port 465 with SSL and port 587 with TLS. See also Use SMTP settings to send mail from a printer, scanner, or app - G Suite Administrator Help[^].

NOTE:
I will edit your question and obscure your account information (especially the password).
   
Comments
paul_vin 8-Sep-17 15:01pm
   
Actually I dont know SMTP server so how can I find @mailserver_name from my PC

in My above case I have found @mailserver_name = 'google-public-dns-b.google.com'
using cmd promt
on command promt I have typed "nslookup" it had given me google-public-dns-b.google.com so for SMTP server How I can find It
Jochen Arndt 8-Sep-17 17:27pm
   
Use the same settings as used in your mail client software for your mail account. For a Gmail account the server is smtp.googlemail.com.

To be honest:
If you don't understand that, you should learn about it or decide to not use such features until you know what you are doing.
paul_vin 11-Sep-17 1:26am
   
Even I tried using
@mailserver_name = 'smtp.googlemail.com' its not working Same Error

I have also tried
@port = 587
@port = 465
but still error
Jochen Arndt 11-Sep-17 3:03am
   
You have to read and understand the Google link from my solution.

Trying various settings without knowing what you do, will not get it working.
The different ports are for different secure connection protocols (SSL resp. TLS) which must be configured too. See the SQL server documentation for sysmail_add_account_sp.

Finally you have to allow Google Mail to accept connections from less secure apps (see https://support.google.com/a/answer/6260879?hl=en).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900