Author Archives: admin

Create Profile and Account using Database Mail Wizard

Prerequisites

  • Must be a member of the sysadmin server role
  • To send email, the user must be a member of the DatabaseMailUserRole
  • Database Mail must be configured and enabled
  • SMTP server address

Create Profile and Account using Database Mail Wizard

  1. Log into the SQL Server using SSMS
  2. Click on the plus sign and expand Management folderImage1_Mgmt
  3. Right-click on Database Mail and select Configure Database MailImage2_RighClickMenu
  4. Click NextImage3_WizardStart
  5. Verify the radio button next to “Set up Database Mail by performing the following tasks:” is selected and click Next.Image4_SetupMailConfig
  6. Click Yes on the PopupImage5_EnableMailXP
  7. On the New Profile Window, fill in the Profile Name and click AddImage6_EmptyMailProfileImage7_PopulatedMailProfile
  8. On the New Database Mail Account Window, fill out Account Name, Email Address, Display Name, Reply Address, Server name, and Port number.     **The Server Name is where you type in the smtp server address.     **The below setup is for Anonymous authentication.  Please check with your email provider if they request Authentication and/or a secure connectionImage8_EmptyMailAccount
  9. Click OK
  10. Click Next
  11. Click NextImage10_DefaultMailProfile
  12. Put a check mark in Public and set Default Profile = Yes and click NextImage11_ConfiguredMailProfile
  13. Click NextImage12_ConfigDBMail
  14. Click FinishImage13_CompleteDBMail
  15. Your configuration is now complete. Click Close.Image14_DoneWithMail

 

Send Test Email

  1. Right-click on Database Mail and choose “Send Test Email”.Image15_SendTestMail
  2. In the Test Email window, fill in a valid email address in the “To:” field and click Send Test Email.Image16_PopulateEmail
  3. Click OKImage17_EmailSentConfirm
  4. Make note of the number in Sent e-mail box. This is the mailitem_id of the test.
  5. Verify test email is received.

 

Troubleshooting Steps

  1. On the Toolbar click New Query to open a Query Editor window.Image18_NewQuery
  2. Execute the following statement to determine the status of the test e-mail message.      SELECT * FROM msdb.dbo.sysmail_allitems where mailitem_id = mailitem_id;Image19_Query
  3. Review the send_status column in the query results to verify the result of the sent email.Image20_EmailResults
  4. If the result is failed. Run the following query.                                                                          SELECT * FROM msdb.dbo.sysmail_event_log where mailitem_id = mailitem_id ;                             *** Use the description field to help you fix the issue.Image21_DescriptionField

 

Written By: Montrial Harrell