SQL Server express edition,We either use CLR integration or configure SQL Mail using MSDB system database.
Here I am discussing sending mail using MSDB system database.By default the MSDB database installed when we install SQL Server. The below tables used to confiure sysmail account.
SELECT *FROM msdb.dbo.sysmail_account
SELECT *FROM msdb.dbo.sysmail_configuration
SELECT *FROM msdb.dbo.sysmail_principalprofile
SELECT *FROM msdb.dbo.sysmail_profile
SELECT *FROM msdb.dbo.sysmail_profileaccount
SELECT *FROM msdb.dbo.sysmail_profileaccount
First of all enable Database Mail XPs through below code to configure database mail.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
And then to configure SQL mail we need to follow below steps.
1. Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA',
@description = 'Sent Mail using MSDB',
@email_address = 'raghwendra.mishra@spluspl.com',
@display_name = 'DBA',
@username='raghwendra.mishra@spluspl.com',
@password='Admin@123',
@mailserver_name = 'smtp.Your Mail server.com'
2. Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA',
@description = 'Profile used to send mail'
3. 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 = 'DBA',
@account_name = 'DBA',
@sequence_number = 1
4. To Grants 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 = 'DBA',
@principal_name = 'public',
@is_default = 1 ;
5. Send Mail using Created Profile.
exec msdb.dbo.sp_send_dbmail @profile_name = 'DBA',
@recipients = 'raghwendra.mishra@spluspl.com',
@subject = 'Mail Test',
@body = 'Mail Sent Successfully',
@body_format = 'text'
After executing above query check your email Inbox you will get mail like:
