What the SQL server database Mail does is to basically send out emails from the SQL Server engine to SMTP servers.
Why would you need that ? Well, let’s see: you might have long running jobs and rather than keeping on checking on them you can set an alert to send you an email when the job is complete. You might need to send out data reports from your database to certain people on a time based period, nothing simpler than that: set a job with a query that will be executed and it’s results will be sent via email to the specified recipients (the results can be displayed in a csv, xls or html file) .
First thing’s first: you need to configure the Database Mail utility. The steps below will show you just how to do that:
1. Go under Management -> Right click Database Mail and select the first option:
2. Then you’ll need to add an SMTP account, you can see below how do add an Gmail , Hotmail and Yahoo SMTP account:
3. Gmail SMTP account:
- Email address: your Gmail address
- Display Name: the name of the address from which you want your emails to appear from
- Gmail SMTP server name: smtp.gmail.com
- Gmail SMTP port: 465
4.Yahoo SMTP account:
- Yahoo SMTP server name: smtp.mail.yahoo.com
- Yahoo SMTP port: 465
5. Hotmail SMTP account:
- Hotmail SMTP server name: smtp.live.com
- Hotmail SMTP port: 25
6. You just click OK after selecting your SMTP account and completing the rest of the details on the dialog window, as the authentication, in my case i went for the basic authentication
7.You then get to set the your profile as public or private, we are gonna go with public so that every user can send email from this profile without explicitly specifying the profile.
8. Then you’re prompted with the window where you need to configure the system parameters, we’re going to leave everything as it is, apart from the Maximum File Size, the default value is 1.000.000 bytes, which is a little under 1MB. Now if you’re planning to also send attachments via email you’ll see that the default value won’t be enough in most cases, so we’ve set our maximum size to approximately 5 MB, which should suffice in most cases. But if you’re planning to send larger amounts of data out of your server then the Database Mail utility isn’t the way to go.
9. After finishing our wizard you can test you setup by simply right-clicking on the Database Mail utility and selecting Send test email:
If you want other informations about the emails going out of your server you can always use the following scripts: http://www.sqlpentruincepatori.ro/sql-server-database-mail-querys/