Many IT professionals have to manage and set up SQL Server Integration Packages (I call them simply SQL Server Jobs) that run on a recurring schedule. The common example is setting up back up jobs that once, every day. Another example might be a job to update statistics that is run on the first Sunday of the month at 1 AM. In addition to these jobs, sql server integration packages you might want to use would to drop and create indexes. If you run SQL Server Standard Edition, note that you cannot select the ‘keep online’ option or else you will encounter an error due to the fact that this is an option only for SQL Server Enterprise License Instances.
In any case, such jobs typically will be set up so that a member of the organization receives a log of the job that is run. You can also set up a notification email, so that a member of your team will receive an email if the job fails (the other options are if it completes, or succeeds).
In setting up database mail on SQL Server 2008 you may have filled in all the required fields accurately only to find when you perform a ‘send test email’ to yourself nothing happens.
First go into sql server logs in the management area. You will notice a check box for Database mail. Uncheck everything else except database mail and see if an error was generated.
The error I encountered was an SMTP error that indicated the mailbox was not available.
After making sure the mail box was working by sending some emails consider what else might cause the problem. Some policy settings that are out of your control.
If your email is from a different domain, (perhaps you work as a contractor on behalf of your client?). For example, your email is ‘email@example.com’ but the organization you work for has emails like , ‘firstname.lastname@example.org’; it may be an indication some policy was put into effect to prevent SMTP Relaying to unsafe email addresses.
So, in a case like this, select a valid email account from a member of the ‘safe domain’ and use it to run the sql server database mail test.
Hopefully you will find that the database mail is received. Keep in mind, organizations are tightening controls on SMTP relays, it is not unreasonable for such policies to restrict outgoing emails to recipients inside their domain. In such a case, the easiest solution is to use an ‘internal email’ address to receive the database mail.