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

Questa pagina è in sola lettura. Puoi visualizzare il sorgente, ma non puoi modificarlo. Contatta l'amministratore se pensi che ci sia un errore.
{{:wiki:icons:t-sql.png?nolink&90 |}} <fs xx-large>SQL Database Mail</fs>
[[hobby:development:sql|{{ :wiki:icons:left_arrow.png?48|Vai alla mappa}}]]\\
//<fc #5F5F5F><fs large>(Configurazione SQL per invio e-mail - Setup iniziale)</fs></fc>//
\\
\\

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 ===

<sxh sql>
-- 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
</sxh>


=== Create profile ===

<sxh sql>
-- 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
</sxh>


=== Create account ===

<sxh sql>
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
</sxh>

=== Link account to profile ===

<sxh sql>
-- 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
</sxh>

=== Test invio e-mail ===

<sxh sql>
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';
</sxh>
                  
=== Controllo dei log e pulizia ===

<sxh sql>
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]
</sxh>