Here is a sample script for configuring database mail in the event you want to automate your SQL installs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
EXEC sys.sp_configure 'Advanced options', 1; RECONFIGURE EXEC sys.sp_configure; USE msdb GO --Step1: Varifying the new profile SELECT * FROM sysmail_profile --Step2: Verifying accounts SELECT * FROM sysmail_account --Step3: To check the accounts of a profile SELECT * FROM sysmail_profileaccount WHERE profile_id = 3 --Step4: To display mail server details SELECT * FROM sysmail_server select * FROM sysmail_allitems |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE msdb; GO EXEC sp_configure 'show advanced options', 1; RECONFIGURE; GO EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE; GO --Creating a Profile EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = 'SQLProfile', @description = 'Mail Service for SQL Server'; -- Create a Mail account for gmail. We have to use our company mail account. EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'SQL_Email_Account', @email_address = 'youremail@gmail.com', @mailserver_name = 'protorelay.pmdomhq.protomold.com', @port = 587, @enable_ssl = 1, @username = 'youremail', @password = 'Emailid password' -- Adding the account to the profile EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'SQLProfile', @account_name = 'SQL_Email_Account', @sequence_number = 1; -- Granting access to the profile to the DatabaseMailUserRole of MSDB EXEC msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'SQLProfile', @principal_id = 0, @is_default = 1; --Sending Test Mail EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLProfile', @recipients = 'youremail@gmail.com', @body = 'Database Mail Testing...', @subject = 'Database Mail from SQL Server from ' --+ @@servername; --Verifying, check status column SELECT * FROM sysmail_allitems |
Views – 2458