Fixing SQL Server 2008 Email Errors

by

SQL 2008 email

So, I am looking at a the logs from a fresh installation of Microsoft SQL Server
2008. This particular database server has just been setup with a daily backup
maintenance plan, and it is spitting error messages which say “Failed
(-1073548784) Executing the query Execute msdb.dbo.sp_notify_operator … failed
with the following error: No global profile is configured. Specify a profile
name in the @profile_name parameter. Possible failure reasons: problems with
the query. Resultset property not set correctly, parameters no set correctly,
or connection not established correctly.” After looking at the backup
maintenance plan, I determined that success and failure emails were configured,
but they were not being received by anyone. So, the above error actually
indicates that SQL Server is having a problem initiating the configured emails.

SQL Server 2008 uses SMTP to generate its emails. Possible failure reasons can
include firewall problems on the server itself, or in the network. This was not
my case. Instead, to fix this error all I needed to do was run the “Database
Mail Configuration Wizard.” The corrective steps were as follows:

  1. Open Microsoft SQL Server Management Studio:
    • Login to the SQL Server machine.
    • Run Programs | SQL Server 2008 | SQL Server Management Studio.
    • Expand “SQL Server Logs”
    • Highlight “Database Mail”
  2. Run the “Database Mail Configuration Wizard”:
    • Right-click and run the “Configure Database Mail” menu item.
    • Click the Next button.
    • Select “Manage Profile Security” radio button.
    • Click the Next button.
    • Use the dropdown to set the “Default Profile” to yes.
    • Click the Next button.
    • Once “Success” has been returned, click the Close button.
  3. Test your email configuration:
    • Right-click the “Database Mail” icon and run the “Send Test E-mail” menu item.
    • The “Send Test E-mail” dialog box appears.
    • Enter a valid email recipient that you can easily monitor.
    • Click the “Send Test E-mail” button.
    • Click the Close button.
  4. Validate the email was received:
    • Put email for the email recipient in step 3 above.
    • If the email was successfully received, your email configuration is done.

Once the above steps were completed, the automatically generated backup
maintenance plan emails started working. And, the above error messages
disappeared from the SQL Server logs.