How to configure Database Mail in SQL Server Express

 

To enable Database Mail

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

Now we can get back to setting up the e-mail profile and adding an e-mail account.

-- Create a Database Mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Notifications', @description = 'Profile used for sending outgoing notifications using MAIL.' ; Go -- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Notifications', @principal_name = 'public', @is_default = 1 ; GO -- Create a Database Mail account EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'MAIL', @description = 'Mail account for sending outgoing notifications.', @email_address = 'youremail@gmail.com', @display_name = 'your email name', @mailserver_name = 'mail.gmail.com', @port = 465, @enable_ssl = 1, @username = 'youremail@gmail.com', @password = 'your password' ; GO -- Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Notifications', @account_name = 'MAIL', @sequence_number =1 ; GO

Now, you can test the email configuration

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'youremail@gmail.com', @query = 'SELECT * FROM msdb.dbo.sysmail_event_log; ' , @subject = 'Email Testing', @attach_query_result_as_file = 1 ;

Check email log

SELECT * FROM msdb.dbo.sysmail_event_log;