hobby:development:sql:database_mail:setup [MLC Wiki]

SQL Database Mail
(Configurazione SQL per invio e-mail - Setup iniziale)

Database Mail è la funzionalità nativa di SQL Server per inviare e-mail via SMTP direttamente da T-SQL (stored procedure sp_send_dbmail).
È l'opzione più comune, e si integra bene con uno scheduler di esecuzione procedure

Enable Database Mail

-- 1) Enable Database Mail feature (disabled by default)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
GO

Create profile

-- 2) Create a Database Mail profile and the SMTP account behind it
EXEC msdb.dbo.sysmail_add_profile_sp
	@profile_name = 'SchedulerMailProfile',
	@description = 'Profile used to send scheduler notifications';
GO

Create account

EXEC msdb.dbo.sysmail_add_account_sp
	@account_name = 'SchedulerMailAccount',
	@email_address = 'notifier@live-tech.com',
	@display_name = 'SQL Server Scheduler',
--	@replyto_address nvarchar(128) = NULL,
--	@description nvarchar(256) = NULL,  
	@mailserver_name = 'mail001.livetech.local' -- your SMTP server
--	@mailserver_type sysname = N'SMTP',
--	@port = 25, -- INT
--	@username = NULL, -- NVARCHAR(128)
--	@password = NULL, -- NVARCHAR(128)
--	@use_default_credentials = 0, -- BIT
--	@enable_ssl = 0, -- BIT
--	@account_id = NULL OUTPUT -- INT
GO

Link account to profile

-- 3) Link account to profile, and make it the default for the msdb database
EXEC msdb.dbo.sysmail_add_profileaccount_sp
	@profile_name = 'SchedulerMailProfile',
	@account_name = 'SchedulerMailAccount',
	@sequence_number = 1;
GO

EXEC msdb.dbo.sysmail_add_principalprofile_sp
	@profile_name = 'SchedulerMailProfile',
	@principal_name = 'public',
	@is_default = 1;
GO

Test invio e-mail

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'SchedulerMailProfile',
	@recipients = 'mauro.luigi.cortese@live-tech.com',
--	@copy_recipients = 'mauro.luigi.cortese@live-tech.com',
--	@blind_copy_recipients = 'mauro.luigi.cortese@live-tech.com',
	@subject = 'TEST',
	@body = 'EMAIL DI PROVA SQL',
	@body_format = 'HTML';

Controllo dei log e pulizia

SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
SELECT * FROM [msdb].[dbo].[sysmail_event_log]


DECLARE @Date DATETIME = GETDATE()
EXEC msdb.dbo.sysmail_delete_mailitems_sp 
	@sent_before = @Date; 
--	@sent_status= '' -- (unsent, sent, failed, retrying).

EXEC msdb.dbo.sysmail_delete_log_sp

SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
SELECT * FROM [msdb].[dbo].[sysmail_event_log]