Email functionality is integrated in almost every website and application now a days. Microsoft SQL Server also supports sending emails. Even though it is not recommended to use SQL Server as your Email Server, but sometimes you come across a challenge where where you have to send email from SQL Server.
Here is the step by step procedure to send email from SQL Server.
First, you need to create an account and profile using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database. However, Database Mail wizard may not be present in the edition you are using. If it is the case, you can always use stored procedure (sysmail_add_account_sp) dedicated for creating account. Execute following stored procedure. Make sure you use correct credentials.
EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'SQLServerEmail', @description = 'Send Emails', @email_address = '[email protected]', @display_name = 'TopSQLServerTips', @mailserver_name = 'sqlservertips.club', @port = 587, @username = '[email protected]', @password = 'ThisIsNotARealPassword' Go
Next step is to create a profile. Execute dbo.sysmail_add_profile_sp with following parameters
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = 'SendEmailProfile' , @description = 'Optional Profile Description' Go
Next Step is to map account created in step 1 to the profile created in step 2. For this, we use sysmail_add_profileaccount_sp stored procedure.
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'SendEmailProfile' , @account_name = 'SQLServerEmail' , @sequence_number = 1 GO
That’s it! We are all set to send emails from SQL Server. You will use sp_send_dbmail stored procedure to send email. For example
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SendEmailProfile' , @recipients = '[email protected]' , @subject = 'An Email from SQL Server' , @body = '<p>This email message is sent from SQL Server.</p>' , @body_format='HTML', , @importance ='HIGH' GO