How to send an email using store procedure on the Azure SQL MI

Question

Hi Team,

Currently, we are migrating all databases from on-premises SQL Server to an Azure SQL Managed Instance. The database send email code is used in some stored procedures to send the status of the table row count. Is it possible to send the email once the code is moved to Azure SQL MI?

SQL Server on-premises: we have set up the database account with the SMTP server. Is it possible to set up a similar database mail service in Azure SQL MI? 

Kindly help me on this.

Answer 1

Thanks for your question.

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine or Azure SQL Managed Instance. Your applications can send e-mail messages to users using Database Mail via an external SMTP server. 

Here is a document for reference.

https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail?view=sql-server-ver16

And the community blog "Sending emails in Azure SQL Managed Instance"

https://techcommunity.microsoft.com/t5/azure-sql-blog/sending-emails-in-azure-sql-managed-instance/ba-p/386235

Let us know if this helps!

Answer 2

Below are the setup steps:

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = '...',
    @description = '...',
    @email_address = '$(loginEmail)',
    @display_name = '...',
    @mailserver_name = '$(mailserver)' ,
    @username = '$(loginEmail)' ,  
    @password = '$(password)' 

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @description = '...' ;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @account_name = '...',
    @sequence_number = 1;

Then enable Database Mail XP on the server:

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

Then you can send emails as shown below:

DECLARE @body VARCHAR(4000) = 'The email is sent with msdb.dbo.sp_send_dbmail from ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail 
        @profile_name = 'AzureManagedInstance_dbmail_profile', 
        @recipients = '$(email)', 
        @body = @body, 
        @subject = 'Azure SQL Instance - test email' ;