Sending Mail using SQL Server Express Edition

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:

About the author:

Raghawendra Mishra is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, he actively contributes to the areas of Technology and Information Security. He can be contacted at raghwendra.mishra@spluspl.com