How can we send email by sql script ?
To enable Database Mail XP
1
2USE msdb;
3GO
4EXEC sp_configure 'Database Mail XPs'
5GO
6RECONFIGURE
7GO
8EXEC sp_configure 'Database Mail XPs', 1
9GO
10RECONFIGURE
11GO
12EXEC sp_configure 'Database Mail XPs'
13GO
To configure database mail
1
2USE msdb
3GO
4
5DECLARE @ProfileName VARCHAR(255)
6DECLARE @AccountName VARCHAR(255)
7DECLARE @SMTPAddress VARCHAR(255)
8DECLARE @EmailAddress VARCHAR(128)
9DECLARE @DisplayUser VARCHAR(128)
10
11SET @ProfileName = 'DBMailProfile';
12SET @AccountName = 'DBMailAccount';
13SET @SMTPAddress = 'smtp.server.com';
14SET @EmailAddress = 'myemail@email.com';
15SET @DisplayUser = 'Myname Noname';
16
17EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @AccountName, @email_address = @EmailAddress, @display_name = @DisplayUser, @mailserver_name = @SMTPAddress
18
19EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName
20
21EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName, @sequence_number = 1;
22
23--Now that everything is setup, I will send a test e-mail.
24EXEC msdb.dbo.sp_send_dbmail @recipients = N'myemail@email.com', @body = 'Test Email Body', @subject = 'Test Email Subject from SQL', @profile_name = 'DBMailProfile'
Comments