How we send email through SQL Server
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
Through Microsoft SQL Server we have the possibility to send emails either for some alerting or so that we can send reports.
In this article we will see step by step how it works and how to activate it Database Mail in SQL Server.
When a user executes the system procedure msdb.dbo.sp_send_dbmail with the email it wants to send, SQL Server forwards it to the SMTP mail server of the respective provider such as outlook, exchange, gmail and then sends it to the end user. This process can be activated either adhoc whenever we call the procedure, either through a schedule in the SQL Server Agent, or by creating alerting Operators in the SQL Server Agent.
Let's see now the steps we will have to follow and the settings we will have to make in order to have this possibility.
The example
For the beginning the example I will use one account at gmail . gmail to allow access from SQL Server we should have gone to its page and enabled allow access to less secure applications from here .
We choose below instance us, Management, Database Mail, right click Configure Database Mail.
Then we select it Setup since we want to create a new Mail Database.
It will then ask us to give a name to the Profile us and we choose Add.
At this point we should fill in all the details of the SMTP provider such as:
- The e-mail address
- The name that will appear as the sender
- The name of the SMTP server, in our case is o smtp.gmail.com who listens at the door 587
- We choose SSL secure connection (required for gmail)
- And in basic authentication, our gmail credentials
In the next step we select it as public and default so that anyone can use it.
At the finish it asks to send a test email to any address we prefer. I wrote my personal gmail and immediately received the following:
Alternatively, we can do the whole process above with T-SQL with the following code:
DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128),
@username nvarchar(128),
@password nvarchar(128),
@enable_ssl int,
@port INT;
SET @profile_name = 'gmail';
SET @account_name = 'gmail';
SET @SMTP_servername = 'smtp.gmail.com';
SET @port = 587 --default 25
SET @enable_ssl = 1
SET @email_address = 'dataplatform.gr@gmail.com';
SET @display_name = 'info@dataplatform.gr';
SET @username = 'dataplatform.gr@gmail.com';
SET @password= 'password';
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (gmail) already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (gmail) already exists.', 16, 1) ;
GOTO done;
END;
BEGIN TRANSACTION ;
DECLARE @rv INT;
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername,
@username=@username,
@password=@password,
@enable_ssl=@enable_ssl,
@port=@port;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (gmail).', 16, 1) ;
GOTO done;
END
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (gmail).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the specified profile with the specified account (gmail).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
sp_configure "show advanced options",1
go
sp_configure "Database Mail XPs",1
go
reconfigure
go
sp_configure "show advanced options",0
go
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
@databasemail_profile=N'gmail',
@use_databasemail=1
GO
After Database Mail is installed how do we send new On Demand?
As we mentioned at the beginning with the use of the systemic procedure msdb.dbo.sp_send_dbmail we can send email.
The parameters needed are:
- The profile_name we made during setup
- The emails that will be sent
- The title of the email
- And the text of the email
- We also have the option to add file_attachments (provided, of course, that the account that launches the service has the right to see the files)
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'gmail', @recipients = 'info@dataplatform.gr', --@file_attachments = 'C:\file.txt', @subject = 'Titlos', @body = 'Keimeno';
How can we see the progress of sending emails
With the following queries we can see respectively:
- Which emails were sent successfully?
- Which is in the process of shipping
- And in which their mission failed
select * from msdb.dbo.sysmail_sentitems; select * from msdb.dbo.sysmail_unsentitems; select * from msdb.dbo.sysmail_faileditems;
