SQL SERVER – Difference Between Database Mail and SQLMail

In recent user group meeting in my city Ahmedabad, I have found that not every user knows difference between these two features of SQL Server. I do not blame any user for not knowing difference between Database Mail and SQLMail as this is very confusing sometime. I will try to explain this concept here.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement 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). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well 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 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 upgrade to Database Mail and start to stop using SQLMail.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

41 thoughts on “SQL SERVER – Difference Between Database Mail and SQLMail

  1. Oh, I see… nevermind my last comment. You were telling them how to turn on the old SQL Mail XPs, not how to turn on the new Database Mail XPs.

  2. Pingback: SQL SERVER - Introduction to Service Broker Journey to SQL Authority with Pinal Dave

  3. Can you please put a blog on how to setup database mail (step by step). I am trying to send response back if a Job fails.

  4. Dear Pinal

    I am using sql server 2005 sp1 but unable to send mail , is there need any aditional software apart from outlook 2003.

    Thanks in Advance

    Regards
    Jayant

  5. @jayant das

    If you are using SQL Server 2005, then you have two ways of sending email through SQL Server.

    1. Database Mail. (Only SMTP)
    2. SQL Mail (Same as SQL Server 2000)

    Database Mail does not require any other software, SQL Server itself can send emails given SMTP Server details provded to SQL Server.

    If you use SQL Mail, you need to use Outlook. Which I don;t suggest for security reasons and alot of other pain giving reasons

    Database Mail is NOT available in SQL Server Express Edition though.

    Go through a step by step procedure (screen shots explanation)

    http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

    ~ IM

  6. Imran, I have seen some of your posts on this blog, U have explained them very clearly and in detail,, like Txn trun

    I have a question regarding DB mail, is there a way that i can either attach error log or give a detailed error message to the recipient mail when a job fails.
    Please let me know if that works on SQL 2005 DB Mail.

    Details: Windows 2003 Enterprise
    SQL: SQL Server 2005 Enterprise with latest SP4

    Thank you,

    Rao

  7. @Rao.

    Thanks for appreciation.

    When you create a job, in response tab you can send alert when Job completes/ succeeds/ fail.

    Usually when a job succeeds or fails you will get a message related to that job in the same alert.

    You can use database mail to send alerts.

    Look at link below, to know more how. For SQL Server 2005, process is exact same as shown in below link,

    Link : http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

    Another workaround if above procedure does not fulfill your needs, then you can create a logfile for job on operating system ( Right Click Job Name, Click Properties, Double Click Step or edit step, click on Advance tab and select an option that suits best) , the next step (set it in a way, if previous step fails, still this step has to be executed) of the same job will be to send that log file created on operating system through an email ( using xp_sendemail). This will also database mail to send email.

    let me know if you have questions.

    ~ IM.

  8. Hi,

    You have mentioned in your post that “Database Mail can be encrypted for additional security”. I have tried to find some information regarding this feature but wasn’t able to locate anything. Would you have any technical information (how does it work and how to implement it) regarding this functionality?

    Thank you for your time.

    Regards,

    Joseph

  9. Sending Emails with DB Mail works fine, but DB Mail can’t read emails! So DB is not a full replacement. Does someone has a suggestion how to read emails?

    Thanks

  10. Hi Alunjd,

    Before you start enabling DB MAIL on SQL 2005/2008, check port 25 is opened on the server to communicate with exchangeserver.
    To do this type in cmd

    1.) telnet exchangeservername 25
    If it gets connected then you can send email.
    2.) Configure Database Mail under Management.
    3.) Creator an operator to send email to RCPT’s
    4.) Under SQL Server agent properties enable mail profile.
    5.) If SQL 2005 then enable DBmail under surface area configuration…….

    Good to goo

    And thanks Imran Mohammed for previous post..

    Rao

  11. We have upgraded from SQL2000 to SQL2008 but by switching to Database Mail from SQL Mail we are now unable to send files in CSV format.

    Can someone explain how we send CSV output from SQL Server via Database mail?

    Thanks

    Andy

  12. Hi Pinal,

    We have SQL Serve 6.5 and I’m trying to set up SQL Mail.

    Can you please provide step by step instructions on how to install it?

    Thanks for your help in advance!

    Regards,
    Joseph

  13. Hi Pinal,

    Good article, but what are the pre-requisites or scenarios where we can make use of this. I wanted to know from a layering point of view.

    I would prefer to have my mail logic with front-end layer rather than loading up the database. Can you please advise.

    Regards,
    Shah

  14. Dear Sir,
    how do i send mail from sql server 2005/2008 express edition as they both do not have the feature of database and sql mail?
    please help…

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

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

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

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

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

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

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

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

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s