Send email by sql
Rédigé par Sozezzo - - Aucun commentaireHow 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'