Email is so popular now. Do you also want to send emails from database applications by using T-SQL statements? Do you want to send a result of T-SQL queries directly to some one else? If you do, Database Mail is the solution to fulfill your needs. This post is a brief introduction that demonstrates how you can use Database Mail to send a simple email in T-SQL statements.
First of all, you need to create a profile that contains all necessary information to send emails. There are two alternative ways to create a profile: UI or T-SQL.
Use UI to create a profile
1) Open SQL Server Manament Studio (SSMS), connect to SQL Server, and expand Management. You will see a picture like below
2) Move the cursor over to “Database Mail” and double-click the cursor to start configuration of Database Mail.
3) Click "Next" to start creating a profile.
4) Click "Next" to continue. If it is the first time to use Database Mail, you may see a message as follows.
This is because Database Mail is not enabled yet. Click Yes to confirm that you want to enable and use Database Mail. Once it is confirmed, the message will no longer occur.
5) Name your profile and fill it in the box as follows.
6) Click "Add" to add an email account into your profile
Database Mail sends emails through SMTP server. The email account is given in the way about SMTP information. If you don't know the server name for SMTP at your site, ask your system administrator for it. This is very important. You must get the server name right. If the server name is not correct, emails will fail to send out. The port number is usually 25 you probably don't need to change.
7) Click "OK" and get back to the profile page.
8) Click "Next" and follow the subsequent next steps to finish creation
You have created a profile successfully! Now let's test if the profile is really a good one.
a) Go back to SQL Server Management Studio and right-click mouse over Database Mail.
b) Select "Send Test E-Mail..." and type your own email address on "To:" line so that you can confirm if the test email is sent to you.
You should receive this test email after a while though the time may vary depending on SQL and email servers.
Use T-SQL to create a profile
Run the following T-SQL to create a profile as same as demonstrated in UI method above
-- Start T-SQL USE [msdb] -- Create a profile EXEC sysmail_add_profile_sp @profile_name = 'MailProfile1', @description = 'A profile contains some email account.' -- Create an email account EXEC sysmail_add_account_sp @account_name = 'MailAccount1', @description = 'The email account contains address, server name, authentication and etc.', @email_address = 'email@example.com', @display_name = 'Sample Name', @replyto_address = 'firstname.lastname@example.org', @mailserver_name = 'smtp.sample.microsoft.com', @port = 25, @use_default_credentials = 1 -- Add the email account "MailAccount1" to the profile "MailProfile1' EXEC sysmail_add_profileaccount_sp @profile_name = 'MailProfile1', @account_name = 'MailAccount1', @sequence_number = 1 -- End T-SQL
Now you have created a profile. You can use it to send emails in T-SQL statements. Below is an example to send an email to someone.
-- Start T-SQL USE [msdb] EXEC sp_send_dbmail @profile_name = 'MailProfile1', @recipients = 'email@example.com', @subject = 'Database Mail Test by T-SQL', @body = 'This is a test e-mail sent from Database Mail on YUHONGLI5\KJINST.' -- End T-SQL
This is the first introduction of Database Mail. More topics will come.
For more information, see http://msdn.microsoft.com/en-us/library/ms175887.aspx
<End> This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
i try this, and the result says "mail queued". But when i check my email, there is nothing displayed such as i have sent using T-SQL.
Please guide me, what's the problems.
May be at my computer configuration or others that need to be configured.
if you don't mind, please send me email at firstname.lastname@example.org
thank you so much
Excellent post - thanks a mil
Great :) Thanks.