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.
Here is the video of the same subject:
[youtube=http://www.youtube.com/watch?v=ZGDBB2uwNp8]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 (https://blog.sqlauthority.com)
478 Comments. Leave new
The one challenge I’m having with guides in PPT 2010 is that the selection defaults to the guides versus the object. So if I’m trying to resize something or delete something and it overlaps with the guides, I have to move the guide before I can select the object (thus sort of defeating the purpose of the guides.) Any idea how to make the guides go into the background and become secondarily selected?
Hiii,
How can I add header in mail from SQL SERVER 2008???
I can’t found anything on this.
Thanks.
Mail not comes on my ID ,All the steps execute successfully but when we execute sysmail log then one exception also occur . So can u tell me what exactly the problem is?
Exception :
Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
Hi Frieds.
I have created a mail step in SQL 2008 and its having attachment link also, the problem is my attachment file is dainamic file name. please help me how to attach dainamic file name in sql mail step.
please help me soon.
hi posarao
For example, if you want to attach files, “d:\Data_20070119.xls”, use this expression in SendMail Task
FileAttachMent
“D:\\” + “Data_” + REPLACE(SUBSTRING((DT_WSTR, 50) GETDATE(), 1, 10), “-“,””) + “.xls”
hi pinal, once again a help ful article by you ,, but first few images have been removed,
thanx sir it was ver nice post…….
very good post Pinal sir but there any solution in sql server 2005 for Mailing..
Please help for same
Hi Pinal,
I have a process which sends mail with attachment (.csv file) through SP sp_send_dbmail. This SP sends mail successfully to user. User can open .csv file in windows outlook mail server. But same file can not open in I-Pad, iphone etc. Quick Look shows error.
Mail sending Envrionment – window XP, SQL Server 2008, file size is 5 kb.
Do I need to change an SQL or window settings.
Your early response is highly appreciated.
Below is the code which I am using to send mail.
—================
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘AutoMail’,
@recipients = ‘ravinder.singh@abc.com;’,
@copy_recipients = ” ,
@blind_copy_recipients = ”,
@subject = ‘Test mail’,
@body = ‘The attach file contains a lis of agencies’,
@body_format = ‘HTML’, @execute_query_database = ‘AU_08102012’, @query = ‘SET NOCOUNT ON;Select AgencyID,AgencyName From [AU_08102012].[Dbo].Tbl_Agency Where AgencyID < 20', @attach_query_result_as_file = 1 ,
@query_attachment_filename = 'NewMyTestFile.csv', @query_result_header = 1,
@query_result_width = 32767,
@exclude_query_output = 1,
@query_result_no_padding = 1,
@query_result_separator = ','
–================
R S Bisht
Ravinder
Hi Pinal,
I have a process which sends mail with attachment (.csv file) through SP sp_send_dbmail. This SP sends mail successfully to user. User can open .csv file in windows outlook mail server. But same file can not open in I-Pad, iphone etc. Quick Look shows error.
Mail sending Envrionment – window XP, SQL Server 2008, file size is 5 kb.
Do I need to change an SQL or window settings.
Your early response is highly appreciated.
Below is the code which I am using to send mail.
—================
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘AutoMail’,
@recipients = ‘ravinder.singh@abc.com;’,
@copy_recipients = ” ,
@blind_copy_recipients = ”,
@subject = ‘Test mail’,
@body = ‘The attach file contains a lis of agencies’,
@body_format = ‘HTML’, @execute_query_database = ‘AU_08102012′, @query = ‘SET NOCOUNT ON;Select AgencyID,AgencyName From [AU_08102012].[Dbo].Tbl_Agency Where AgencyID < 20', @attach_query_result_as_file = 1 ,
@query_attachment_filename = 'NewMyTestFile.csv', @query_result_header = 1,
@query_result_width = 32767,
@exclude_query_output = 1,
@query_result_no_padding = 1,
@query_result_separator = ','
–================
R S Bisht
Respected Sir,
I have a prob regarding dbmail and i have mailed you my all problem at ur mail id.
Sir..kindly read my problem and reply me
I will be very thankful to u
working fine
Here is how you can have alternating colors for the output table
create table #x (id int identity(1,1), first_name varchar(20))
insert into #x
select *
from
(
select ‘Mohammad’ as first_name
union all
select ‘Ahmad’ as first_name
union all
select ‘Mahmoud’ as first_name
union all
select ‘Mustafa’ as first_name
union all
select ‘Taha’ as first_name ) b
select case when ROW_NUMBER() OVER(ORDER BY id DESC) % 2 = 0 then ‘background-color:silver’ else ‘background-color:white’ end as ‘@style’,
first_name as ‘td’
from #x
FOR XML PATH(‘tr’), ELEMENTS
hi all. this is simple script for test
———————————————
— Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘Sample Public Account’,
@description = ‘Mail account for use by all database users.’,
@email_address = ‘user1@sample.com’,
@replyto_address = ‘user2@sample.com’,
@display_name = ‘sample Automated Mailer’,
@mailserver_name = ‘mail.sample.com’ ;
— Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘Sample Public Profile’,
@description = ‘Profile used for administrative mail.’ ;
— Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘Sample Public Profile’,
@account_name = ‘Sample Public Account’,
@sequence_number =1 ;
— Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘Sample Public Profile’,
@principal_name = ‘public’,
@is_default = 1 ;
–Step 2) Configure Email:
sp_CONFIGURE ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO
–Step 3) Send Email:
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’Sample Public Profile’,
@recipients=’user2@sample.com’,
@subject=’Test message’,
@body=’This is the body of the test message.
Congrates Database Mail Received By you Successfully.’
thanks for such a nice n helpful doc
I set database Mail in SQL2008 R2 server but when I sent an email I receive this error
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 . Exception Message : could not connect to mail server. (the requested name is valid but no data of the requested type was found )
Any help please
same prob :( any solution?
Hello Dave,
By any chance, can I update some columns on table sysmail_mailitems in order to force the mail client ro resend an email (for eg. I was thinking about updating the column sent_status to 0)
Thank you!
Hi… hope you can help me…
I’ve done all you said… but i’m getting this error msg:
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-01-07T11:17:59). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 172.16.210.10:25).
)
Regards!!!
BTW: Happy New Year to all of you!!!
Doug
This was very helpful and worked the first time around.
Good article Dave.
Hi Dave,
I have an ssis package that sends out remittances after a bank transfer. It works, but one of the recipients does not always receive his email with attachment. Since the email addresses originated in Great Plains, I have verified that they are entered correctly and have added my own email to this particular account so that I can see if I receive a copy when the other person should receive one. If he receives it, so do I. If he does not receive it, neither do I and my email address is internal. I also have alerts/notifications set up for jobs that work most of the time, but not all. I receive one or the other of these two error messages:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2013-01-11T14:29:55). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-07-29T09:01:30). Exception Message: Could not connect to mail server. (No such host is known). )
I have been Googling all day and still have not resolved this issue. I have read everything from recreate the database mail profile to check the firewall and port settings, etc. I hope you can suggest something for me to try. We have SQL Server 2008 on a Windows 2008 Server, using SMTP.
Please help!
Arlene
Database Mail is not supported for SQL 2008 R2 Express Editions.