SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

August 23, 2008 by pinaldave

Today in this article I would discuss about the Database Mail which is used to send the Email using SQL Server. Previously I had discussed about SQL SERVER - Difference Between Database Mail and SQLMail. Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE ’show advanced’, 1 GO RECONFIGURE GO sp_CONFIGURE ‘Database Mail XPs’, 1 GO RECONFIGURE GO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb GO EXEC sp_send_dbmail @profile_name=‘PinalProfile’, @recipients=‘test@Example.com’, @subject=‘Test message’, @body=‘This is the body of the test message. Congrates Database Mail Received By you Successfully.’

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER - Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.

Let me know what you think about this tutorial.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | Tagged SQL Mail | 34 Comments

34 Responses to “SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database”

on August 23, 2008 at 7:54 am Imran Mohammed

Hello,

Its really Good. I appreciate Monica’s patience in getting all the pics.

Also, the moment you right click database mail, and you try to configure it, and this feature is off ( default setting ) then you will get a dialogue box saying do you want to on this setting…

As a learner I would also like to see where we will be using this useful feature of SQL Server 2005. The one good reason where we can use Database mail is to send alerts.

It would be good if you also add how to sent alerts using database mail.

1. Create operator,
2. In response to job, sent email to this operator ( this email will be sent by database mail).

I know I am asking too much, I still appreciate her for spending so much time, I use database mail every day, but I learnt some new things in this post.

Thanks Monica and Pinal Dave.

Imran.
on August 23, 2008 at 10:09 am pinaldave

Imran,

I appreciate your suggestions. I totally agree with you what you suggested and I will follow up on it.

Create operator is something on my list and I will have that done very soon. Sending important SQL Server related alerts to operator is very crucial task and this article is base for it.

Again, thank you for your words and I will work on your suggestions.

Kind Regards,
Pinal
on August 23, 2008 at 5:32 pm Nilesh

Dear pinaldave

I m getting an exception after executing the procedure sp_send_dbmail as below

Cannot send mails to mail server. (The operation has timed out.). )

Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The Transaction not longer valid.
on August 23, 2008 at 5:35 pm Roger Ben Silva

Nilesh,

Are you using SQL Server 2008? It works great for me.

Thanks Pinal.

Great work.
on August 23, 2008 at 5:37 pm Mike Aaron - Microsoft MVP

Good Work! This is very helpful post.

Imran you are correct. Monica good pics.

Mike Aaron - Microsoft MVP
on August 27, 2008 at 12:37 pm Sandeep

As i’m newbie to sql server i started with this new feature of sql server 2005 (i.e. using SMTP not MAPI) i got stuck with few things as my sql server 2005 has the default settings.

firstly enable database stored procedures.

start - programs - microsoft sql server 2005 -
configuration tool - sql server surface area configuration - surface area configuration and features

the two more things what we have to keep these things in mind while configuring database mail:

* by default port for database mail is 25.
so open that port in the firewall.

* grant permissions to relay e-mail through SMTP

start - administrative tools- Internet Information Services - expand local computer - default SMTP virtual Server Properties
- thn click tab Access thn Relay - add -
then enter IP address - click ok

Now i guess it would definately work. please correct if i’m wrong.

Sandeep-MCTS
on August 27, 2008 at 4:12 pm Sandeep Charaya

– using T-SQL
use msdb

declare @profile_id smallint

exec msdb.dbo.sysmail_add_profile_sp
@profile_name=’DATABASEMAIL’,
@description=’DATABASEMAIL’,
@profile_id=@profile_id output;

select @profile_id

–exec msdb.dbo.sysmail_help_profile_sp select * from msdb.dbo.sysmail_profile

declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name=’sandeep’,
@email_address=’s.charaya@server.com’,
@display_name=’sandy’,
@description=’kuch bhi’,
@account_id=@account_id output;

select @account_id
/* to update
declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id=1,
@account_name=’sandeep’,
@email_address=’s.charaya@server.com’,
@display_name=’ElixirCT’,
@description=’Sandeep Charaya’,
@replyto_address=’s.charaya@gmail.com’,
@mailserver_name=’server name’,
@mailserver_type=’SMTP’,
@port=25,
@username=null,
@password=null,
@use_default_credentials=1,
@enable_ssl=0;
exec sysmail_help_account_sp
*/
–select * from sysmail_account exec sysmail_help_account_sp
–exec sysmail_help_profile_sp exec sysmail_help_account_sp
/*

exec msdb.dbo.sysmail_add_profileaccount_sp

@profile_id=1,
@account_name=’sandeep’,
@sequence_number=1

–or
@account_id=1,
@profile_name=’DATABASEMAIL’,
@sequence_number=2

*/

–exec sysmail_help_profileaccount_sp
–make the profile public

exec sysmail_add_principalprofile_sp
@profile_name=’DATABASEMAIL’,
@profile_id=1,
@is_default=0,
@principal_name=’public’

–exec sysmail_help_principalprofile_sp

exec msdb.dbo.sp_send_dbmail
@profile_name=’DATABASEMAIL’,
@recipients=’s.charaya@gmail.com’,
@subject=’hello…test mail’,
@body=’hi,
h r u’

SELECT * FROM sysmail_allitems

i hope it works !!
on August 28, 2008 at 9:51 am thomaszae

Hi Pinal Dave,

Actually it’s not different much with SQL 2005, isn’t it :)
What I’ve written in my previous post –>
http://thomas.wordpress.com/2008/08/26/send-mail-sql-server-2005/

was only kind of shortcut so that I don’t have to open the wizard, and when I red your post, the wizard looked the same as SQL 2005 (I’m not yet explore SQL 2008 because my latest software version, Ms Dynamic SL, is still using sQL 2005)

Thank you for informing :)
on August 28, 2008 at 5:07 pm Jitendra Gupta

Really a good post. I want to ask one thing, can we take backup of database using Job, and then send the backup as attatchment to a particular email ID using Database mail. This will provide a backup to the superadmin who is in fact a nontech person. My database is very crucial and have encrypted all the varchar column using 128 bit encryption. There is requirement of the highest degree of protection of data as well as database.

To achieve this the work to be done in sequence (as it think):
1. Create profile
2. Create job for Backup
3. Create job for sending mail.

Can you help me out how to achieve all these.

Regards,
Jitendra Gupta
on August 28, 2008 at 6:24 pm Jitendra Gupta

Hi Pinal,

I have created a maintenance Plan for database and Log backup in SQL Server 2005 using Wizard. But it does not execute at the time specified. When I ran it mannually it gave error like:

“Execution failed. See the maintenance plan and SQL Server Agent job History logs for details.

Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.”

When I peeped into the Job History Log, there was nothing under the maintenance plan.

Please let me clear where, what I am missing.

Regards,
Jitendra Gupta
on August 30, 2008 at 3:24 am MSC

Excellent Post - thanks.

I successfully configured as above several days ago in a new production SQL 2005 cluster. My problem is that the Test function is not available for the SQL Agent configuration unless using SQL Mail AND the job notifications fail…?

The actual Database Mail configuration test works fine.

Any ideas?
on August 30, 2008 at 4:37 pm Zakeer

Hello friends…

Great work by monica. I have done all the things mentioned by monica. But finally i am getting an error saying that

“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-08-30T16:31:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )”

how to overcome this.

Thanks in advance

Regards
Zak
on September 2, 2008 at 10:20 am Himanshu

Hello Pinal,

How can we send mail in ibm db2/400 (database of as/400)?

Thanks and regards,
Himanshu
on September 6, 2008 at 3:43 pm Narayan

Hello Pinal and Zakeer,

Mr. Zakeer, i am getting the same error as u had.

“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-08-30T16:31:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )”

how to overcome this.

Thanks in advance
Narayana
on September 8, 2008 at 10:46 am Imran Mohammed

@ Narayan,

By default we will use port 25 to configure database mail, which is usually blocked by windows firewall for security reason,

try the following.
1. Disable firewall and test if databasemail sends email.
2. Make an exception in the windows firewall.

Or this also happens when you use wrong port numbers,

Just as an example use these values when setting up a database mail feature: You need to have a gmail account, which can be created free if you go to gmail.com, you can send email using gmail service free of cost :) So you can try this example and it works perfect.

In database mail, first create profile, and when you create an account, use these values.

Give Account name: SQL Server Alerts System
Description: XYZ

Outgoing mail Server ( SMTP)

email id: youremailid#gmail.com ( should have gmail.com)
Display Name: SQL Server
Reply email: can leave blank
Server name: smtp.gmail.com ( this is really important)
port no: 587 ( on many website this is given as 465 which is wrong, use 587 )
Check : This server requires a secure connection
Check Basic Authentication
username: youremailid@gmail.com ( should have gmail.com)
password: ( top secret , dont display ) … ;)
confirm password: confirm your password

click next and also you need to make it default public profile.

After you do this, you also have to change SQL Server Agent properties, in Properties click alerts system and then select database mail.
MOST IMRORTANT when you make changes, to SQL SERVER AGENT properties, restart only SQL SERVER AGENT.

right click SQL Server Agent and click restart, and then test your database mail.

to test, right click database mail and click send test mail… VERY IMPORTANT ( Select right profile/account from drop down list), put any email id and click send test email. click ok.

Right click database mail, select view database mail logs keep refreshing … to see if any error message had occured. mean while look in your inbox if you recieved your email.

In few days I will take screen shots on how to configure database mail, taking a live example.

All with GUI’s no scripts :)

Hope this helps.
Imran.
on September 24, 2008 at 10:31 pm srinivas

after configuring the datamail, when i send the test mail using send test mail tab..i am getting following error.
exception mesgae:can not send mails to mail server, mailbox is unavailble.
server response was 5.7.1;client does nto ahve permssions to send as this sender,

am using the sql service acoutn.

how do i knwo, wthether my account is having mailbox or not or do we need mailbox user account.

Regards
Srinivas
on September 26, 2008 at 5:54 pm Jakob H. Heidelberg

Hi Srinivas

My guess is, that you are not allowed to relay on the (SMTP) server you are using. Make sure the SQL servers IP address is allowed to relay on the SMTP/mail server - og use Basic Authentication with a username/password.

Best regards

Jakob H. Heidelberg
MVP:Enterprise Security
on October 1, 2008 at 6:03 pm Navneet Nagpal

Hi thanks, a lot for posting solution for sending mail.
on October 11, 2008 at 1:45 am Louis

Can anyone help me with this?

the following script which returns the count of cases in the support queue and the date/time of the oldest case. Then it emails me the stats.

It works fine when I run from the query window, but when I run as a step in the job, the @body1 assignment is lost and I have no body in my email. Do you have any ideas about why the job appears to lose the variable assignment?

declare @CasesInSupportQueueCount varchar(10)
declare @OldestCaseInSupportQueue DATETIME
declare @body1 varchar(100)
declare @CRLF char(2)

SET @CasesInSupportQueueCount = (SELECT COUNT (qi.queueitemid) AS CasesInSupportQueueCount
FROM FilteredQueueItem as qi
JOIN FilteredQueue as q
ON qi.queueid = q.queueid
WHERE q.Name = ‘Support’ AND qi.objecttypecodename = ‘Case’)

SET @OldestCaseInSupportQueue = (SELECT MIN (qi.createdon)
FROM FilteredQueueItem as qi
JOIN FilteredQueue as q
ON qi.queueid = q.queueid
WHERE q.Name = ‘Support’ AND qi.objecttypecodename = ‘Case’)

SET @CRLF = CHAR(13) + CHAR(10)
SET @body1 = ‘Queue Statistics: ‘ + @CRLF +
‘Queue Count: ‘ + @CasesInSupportQueueCount + @CRLF +
‘Earliest in Queue: ‘ + CAST(@OldestCaseInSupportQueue AS varchar(30))

exec msdb.dbo.sp_send_dbmail
@recipients = ‘Email@email.com’,
@subject = ‘CRM QUEUE STATISTICS’ ,
@body = @body1,
@body_format = ‘TEXT’,
@profile_name = ‘Admin’

RESULTS WHEN SCRIPT IS EXECUTED FROM QUERY ANALYZER WINDOW:

Queue Statistics:
Queue Count: 7
Earliest in Queue: Otc 9 2008 1:59PM

RESULTS WHEN RUN AS JOB. (There is only one step in my job.)
Email sent but no body is display

IF I CHANGE
@body = @body1,
TO
@body = ‘just a test’,
On the email I get:

Just a test

Thanks
on October 16, 2008 at 11:27 am Hari

Thanks Pinal , Monica.
Good Article .Really helpful .

Rgds,
Hari
on October 24, 2008 at 12:57 am notShai

hello, i followed the procedure and i am able to send email from the server successfully.
HOWEVER - i cant get the Jobs to use the db mail feature - when i go to Job Properties — Notifications, check the E-mail box the drop down is EMPTY ! nothing to select from although i have the db email profile setup and working.
any ideas? (i restarted the agent, didnt help)
on October 31, 2008 at 7:45 pm Doris Sebe

Wow

Very,very interesting, i never had of Db Mail before.

I am currently using VB .NET 2008.

My question is, is this what i need to send emails.
Can i get data from the database.
Do you something i can us when i want to send SMS?
on October 31, 2008 at 7:49 pm Doris Sebe

How do send email from Microsoft Access using VB .NET 2008?

Thank you
on November 7, 2008 at 3:57 pm Atanu

Imran you promised screenshot…..

Where Are they?

Your Solution is Not working for me….

Its giving error

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2008-11-07T15:23:49). Exception Message: Could not connect to mail server. (No such host is known). )
on November 13, 2008 at 3:26 am marshall

Hi Dave & All,

I want to implement a trigger that will send email using database mail whenever anybody creates a new database on a server without letting me know aobut it.Can anybody give me the detail to do it please?
Thanks

!!!!the posts are awesome here.

Marshall
on November 13, 2008 at 9:13 pm marshall

Hi Dave & All

I configured database mail to send me emails when job fails.When the job fails and emial is sent to me on the subject box it says’job completed’.same thing appear if i configure database mail to send email when the job succeeds or completes.How to make the ’subject’ to say ‘job failed’ when the condition was ’send email when job fails’ and ‘job succeed’ when the condition is ’send email when job succeeds’?
Thanks
Marshall
on November 13, 2008 at 10:38 pm Cesar

Hello, I need to create a database that will contain student’s grades for different subjects and different grades. Then I need to send each student their individual grades only. How can I achieve that?
on November 14, 2008 at 12:34 pm Saagar

Hello Pinal Dev

Thanx for ur this gr8 help.

Some days before i just heard abt Database mail.
I fired query in Google (thanx google :) )
I got ur article.
It works finly.

Just one silly question :
If i want to send mail with HTML tags so what to do for that ?

Thanx again to you and Monica also

Regards,
Saagar
on November 14, 2008 at 12:39 pm Saagar

Hi Pinal,

I got the solution for HTML Tags Mail format

Thnax

Regards,
saagar
on December 1, 2008 at 5:51 pm Amy

Hi, I have tried setting up database mail (SQL SERVER 2005) before seeing your article, and it is the same thing basically that I did (your gmail example).
I get the error:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-12-01T07:01:53). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required.

I use this exact same account and settings for smtp in C#/ASP.NET and it works there.

Here are my configurations:
- enabled Database Mail stored procedures
- created a new account (smtp.gmail.com, port 587, checked SSL required, valid username, valid password)
- created a new profile that uses that account (public, default)
- send test email (valid email)…

Am I missing something? Do I need to manually add user to DatabaseMailUserRole? (somehow that role did not exist, I had to add it manually)
Do I need to setup an SMTP server in IIS?

Thanks,
Amy
on December 2, 2008 at 2:02 pm Nitin Reddy

@Amy:

You don’t have to setup SMTP on IIS, unless you are planning to use it instead of GMail’s SMTP server.

If you do setup an SMTP server on IIS, then you’ve got to specify “localhost” instead of “smtp.gmail.com”.

-Nitin
on December 6, 2008 at 4:34 am Amy

Thanks Nitin,

So do you have any idea why it’s giving me an ‘authentication required’ error?
on December 6, 2008 at 8:55 am Hany

What about reading emails? Is it true that Microsoft will, or did, disable this functionality ?

If so this will be very Interesting logic… If you can’t secure it then kill it… Well…I hope I am wrong, I have an application that reads and process email in 2000 and hoped to upgrade… Any feedback?
on December 20, 2008 at 4:12 pm Yogesh Jaju

How to create Scheduling of SQL Email?
I wish to have email generation from SQL Server daily at 0900 with particular data from database.

….