Send email by sql

Rédigé par M Sozezzo - - Aucun commentaire

How can we send email by sql script ?

 

To enable Database Mail XP

USE msdb;
GO
EXEC sp_configure 'Database Mail XPs'
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs'
GO

 

To configure database mail


USE msdb
GO

DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'smtp.server.com';
SET @EmailAddress = 'myemail@email.com';
SET @DisplayUser = 'Myname Noname';

EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @AccountName, @email_address = @EmailAddress, @display_name = @DisplayUser, @mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName, @sequence_number = 1;

--Now that everything is setup, I will send a test e-mail.
EXEC msdb.dbo.sp_send_dbmail @recipients = N'myemail@email.com', @body = 'Test Email Body', @subject = 'Test Email Subject from SQL', @profile_name = 'DBMailProfile'

Fil RSS des articles de ce mot clé