In a recent user group meeting in my city Ahmedabad, I have found that not every user knows the difference between these two features of SQL Server. I do not blame any user for not knowing the difference between Database Mail and SQLMail as this is very confusing sometimes. I will try to explain this concept here.
Database mail is a newly introduced concept in SQL Server 2005 and it is the replacement of SQLMail of SQL Server earlier version. It has many enhancements over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Mail depends on Service Broker so this service must be enabled for Database Mail. It can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well as anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.
In summary, I suggest if you are using SQLMail, it is the right time to upgrade to Database Mail.
If you still want to use SQLMail you will have to enable it with specific commands.
EXEC sp_configure 'SQL Mail XPs', 1 GO RECONFIGURE GO
Again, I suggest upgrading to Database Mail and start to stop using SQL Mail.
Reference: Pinal Dave (https://blog.sqlauthority.com)
47 Comments. Leave new
I was very pleased to find this site !!! Did you notice that the Surface Area Configuration tool isn’t provided with MS SQL Server 2008? Configuring Database Mail through SQL Server Management Studio let’s you enable Database Mail…
Thanks for sharing this nice info………..
I absolutely admire your blog. I can see you are putting a lot of effort and hard work on your posts, I’m sure I’d visit here more often.
Thanks for sharing this nice info………..
hi Pinal Sir
Is this possible to send Database Mail when I am using shared database hosting and my db host has no “server management studio” extension, i mean i can’t use this.
is this possible now?
Hello i want to enable sql mail in sql server 2005 express .
i want to start change SQL Mail to Database mail . can i active both sql mail and database mail , then only change sql mail code to database code one at a time.
Hi Pinal,
i want to use database mail.i configure sql server.i can sent test mail from management studio but i use ” EXEC msdb.dbo.sp_send_dbmail” stored procedure mail unable received.When i looked for the sysmail_allitems table in msdb database,i sent the mails status is sent.
How can i solution this sitution ?
Hi Pinal,
What is the main difference between sql mail and database mail.Is this possible to send mails using SQL mail with SMTP
Thanks in advance
i send the email from sql server i am using smtp.gmail.com server name the email is not send exception mail server could not conntect ?? please help me
Hi Pinal,
I cant see the database mail from list to configure in sql server 2008…Could you please tell me how to do it???
In SQL Server Management Studio, in object explorer go to Management -> Database Mail
no, i didn’t find it
Yes. Even I didn’t find it.
only 3 options are there under Management –> using MS SQL SERVER 2008.
But I can able to send mails in MS SQL SERVER 2008 R2.
Please reply ASAP.
Hi Pinal,
I have a process which sends mail with attachment (.csv file) through SP sp_send_dbmail. This SP sends mail successfully to user. User can open .csv file in windows outlook mail server. But same file can not open in I-Pad, iphone etc. Quick Look shows error.
Mail sending Envrionment – window XP, SQL Server 2008, file size is 5 kb.
Do I need to change an SQL or window settings.
Your early response is highly appreciated.
Below is the code which I am using to send mail.
—================
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘AutoMail’,
@recipients = ‘ravinder.singh@abc.com;’,
@copy_recipients = ” ,
@blind_copy_recipients = ”,
@subject = ‘Test mail’,
@body = ‘The attach file contains a lis of agencies’,
@body_format = ‘HTML’, @execute_query_database = ‘AU_08102012′, @query = ‘SET NOCOUNT ON;Select AgencyID,AgencyName From [AU_08102012].[Dbo].Tbl_Agency Where AgencyID < 20', @attach_query_result_as_file = 1 ,
@query_attachment_filename = 'NewMyTestFile.csv', @query_result_header = 1,
@query_result_width = 32767,
@exclude_query_output = 1,
@query_result_no_padding = 1,
@query_result_separator = ','
–================
R S Bisht
Hi Pinal,
How to use outlook smtp address in Database mail? Please help me asap.
Thanks
Phani
Hi Pinal,
Thanks for your effective details. I am using Sql server 2008 R2 and using db mail feature.
Problem is when i send a mail attachment is nearly 8 MB. So now how can i zip the attachment and send it. I am not finding any answer from the blogs.
Is zipping the attachment feature is available in Sql Server 2012 ?
Hi Pinal,
Is there any way to send email by running SPs?
Hi I am trying to send data as a result fo a query to user in text format with specified number of fillers. I am using sp_send_dbmail . The text file in the email is returning data with line breaks.
I don’t want line breaks. i have specified query width as 801 characters.
Hi, I am sending mail using SMTP in SQL server stored procedure. Now I need to check whether the email address is valid/active or not. Email address format is okay. For example, abc@gmail.com is a email address where mail will be sent. The format is okay but before sending mail I need to check that the address is active. If address is valid valid, then the mail will be delivered.
Can any one help me?
You need to write custom logic to check status of mail sent.
Hi, Is there any way to get alert in the form of text on cell phone ??
It depends on provider. There are few providers which can send sms when a email is received. I don’t think its there in India.
https://en.wikipedia.org/wiki/SMS_gateway
Hi,how to configure sql server 2008 database mail with account microsoft exchange
you need to know SMTP Server details. UI is pretty easy to use.
Hi, I am facing issue in existing database mail config, I need to change mail host which is having TLS security and database mail is not working with TLS. Any suggestion ?
I have tried “telnet mailserverhostname 587” and its working. But mail gets fail to sent. I get below error message.
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 2 (2016-06-30T16:30:41). Exception Message:
Cannot send mails to mail server. The remote certificate is invalid according to the validation procedure.
Thanks in advance.