2

I need to use SQL Server Database Mail feature in my project. However Database Mail seems not available on my development instance.

There should be a 'Database Mail' node in the Object Explorer. In this screenshot I can't seem to find any. Also shown is my SQL Server version.

enter image description here

Is Database Mail not supported in this SQL Server version? Which SQL Server version (or edition) required to use Database Mail?

All documentation I can find online always assume the feature is already installed and just go on explaining how to configure it. If my SQL Server version supports Database Mail, how to install/enable the feature?

Improve this question
  • 1
    Why are you using such an old version of SQL Server Express? Why are you using such an old version of Management Studio? – Aaron Bertrand Sep 12 '19 at 12:46
6

You can use SQL Mail with SQL Server Express edition.

Borrowing liberally from Sending Mail using SQL Server Express Edition

To configure SQL mail we need to follow below steps.

--Create Sysmail Account
--Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail  account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'YourEmail@test.com',
@display_name = 'DisplayName',
--@username='umashankar@queryingsql.com',
--@password='password',
@mailserver_name = 'mail.server.com'

--Creating Database Profile
--Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MailTest',
@description = 'Profile used to send mail'

--Add database Mail account to profile
--Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailTest',
@account_name = 'MailTest',
@sequence_number = 1

--Grants permission for a database user or role to use a Database Mail profile.
--To Grant permission for a database user or role to use a Database Mail profile use sysmail_add_principalprofile_sp.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MailTest',
@principal_name = 'public',
@is_default = 1 ;

--Enable 'Database Mail XPs'.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

--Send Mail using Created Profile
exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'receiver@queryingsql.com', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'

I will say that SQL Mail is deprecated and may be removed in a future version of SQL Server Express, but it still seems to work in the latest edition.

Improve this answer
  • Thanks! Saves me a ton not having to setup another instance. Question tough. If enabled this way, will all Database Mail functionalities be the same as if it's built in in the edition? Will there be any differences in function/sp names? – bluearth Sep 12 '19 at 11:43
  • I can't fully answer that question as I have not extensively tested SQL Mail. If I were you, I would set up a variety of quick test scenarios for sending mail (attachments, query results, etc.) and test them out – Scott Hodgin Sep 12 '19 at 11:47
  • My use case is not that complicated anyway. Just making sure sp_send_dbmail accepts all the params and works as documented. To this end, I dont see any incompatibilities – bluearth Sep 12 '19 at 12:00
  • Really, I just came to a late realization that you are referring to SQL Mail and not Database Mail and that the two are different SQL Server features. And that the two has different prerequisites and setups. Just so it happens SQL server keepssp_send_dbmail consistent between those two delivery methods. – bluearth Sep 13 '19 at 4:03
5

Is Database Mail not supported in this SQL Server version? Which SQL Server version (or edition) required to use Database Mail?

Database mail is not included when using SQL Server Express edition: enter image description here Source

To be able to use database mail, you could opt for Web, Standard or Enterprise edition. In non production environments you could also go for developer edition.

Improve this answer
  • Thanks for the source! Sorry to have to revoke your accepted solution though, but @scott-hodgin 's answer is the one I actually taken – bluearth Sep 12 '19 at 11:46
  • 1
    @bluearth: if database mail is not included in SQL Server Express you are probably violating the license terms if you activate it. – a_horse_with_no_name Sep 12 '19 at 11:47
  • @bluearth No problem man, it was my mistake for not providing additional solutions. Thanks for getting back to me about it :) – Randi Vertongen Sep 12 '19 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.