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://blog.SQLAuthority.com)
pinaldave
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.
this worked like a charm and thank you for providing such a great breakdown on how to do this. I have set up one of our servers without a hitch but another server, which is virtual gave me the following error:
*****************************************
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-11-19T08:34:02).
Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 …
Relaying denied. IP name possibly forged [10.1.
*****************************************
Is this because it is virtual or do you think there is something going on with the specific IP?
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
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.
Nilesh,
Are you using SQL Server 2008? It works great for me.
Thanks Pinal.
Great work.
Good Work! This is very helpful post.
Imran you are correct. Monica good pics.
Mike Aaron – Microsoft MVP
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
– 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 !!
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 :)
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
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
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?
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
Hello Pinal,
How can we send mail in ibm db2/400 (database of as/400)?
Thanks and regards,
Himanshu
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
@ 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.
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
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
Hi thanks, a lot for posting solution for sending mail.
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
Thanks Pinal , Monica.
Good Article .Really helpful .
Rgds,
Hari
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)
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?
How do send email from Microsoft Access using VB .NET 2008?
Thank you
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). )
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
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
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?
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
Hi Pinal,
I got the solution for HTML Tags Mail format
Thnax
Regards,
saagar
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
@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
Thanks Nitin,
So do you have any idea why it’s giving me an ‘authentication required’ error?
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?
How to create Scheduling of SQL Email?
I wish to have email generation from SQL Server daily at 0900 with particular data from database.
….
how to check the date in database & send the mail using c#
Hey Pinal
Can you set up the email so that it is not automatically being sent but rather being sent at a programmed time and date? and how can this be done for world time zones so that the email is set to arrive in your inbox at a particular time say 9am on the 10 January 2009 in your time zone?
Thanks
I just want to say that your work (not only on this topic) is great!!!!!
Hi everybody,
I configured the datamailfollowing the above steps…
when i am trying to send a test mail, i m getting following error…
please anybody can help me regarding this. i need to finish this task immediately.
Error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2009-02-05T10:52:07). 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.7.0 Authentication required).
Thanks
Mohan
Hi,
I want to send an SMS using this feature.
when i try to send SMS(9198xxxxxxxx@airtelkk.com) using webmail i’m getting the SMS clearly. but when i try the same using database mail am getting some junk value or may be the encrypted form.
How to overcome this..?
plz help……
Thanks in advance..
Pramod..
Saagar Wrote :
“——-”
Saagar
Hi Pinal,
I got the solution for HTML Tags Mail format
Thnax
Regards,
saagar
“——”
What is the solution? Please share.
is outlook is required for the database mail to be working..????
Hey, Pinal:
I think you should publish a similar article with the total GUI explanation given by Imran Mohammed. My attempt to config settings/permissions on a VISTA localhost had all kinds of blocking problems and I could never get passed “actively blocking” message.
But I followed Imran Mohammed’s clear explanation above and no problems!
Might be useful for other developers fighting with Vista Firewall, SMTP, etc.
Thanks!
How do you Receive email into SQL Server? If we get an email to support@ how can we set it up to receive into our SQL Server database?
Hi Pinal,
how are you?
I did that same as your showing. However, I cannot recieve the email in Inbox. The error in log is “…because of the mail server failure.” but the server did successfully pass in Database Mail Configureation Wizard.
Please tell me how can handle that issues.
Thank you,
Frank
Does anyone know if SQL Express 2008 has e-mail functionality? I do not see the “Database Mail” anywhere.
I know the full version has a wizard for this, but I am unable to locate or find documentation that supports e-mail with the Express version. Any assistance would be greatly appreciated.
Thank you.
Mark
@Mark A. Ross
Database Mail is only available in following Editions of 2008.
1. Enterprise Edition
2. standard Edition
3. Workgroup
4. Web
Express Edition does not have database mail feature.
Look under Manageability section for more details in below link
Link : http://msdn.microsoft.com/en-us/library/cc645993.aspx
Suggestion: Why dont you use developer Edition or Evaluation copy.
IM.
Hi Pinal Dave,
I have tried to Configure Database Mail in my SQL server 2005 with my gmail account, but it always shows
Mail queued.
It not sends the mail to receipent email account.
Can u help me out, where I am doing the mistake while configuring the database mail.
Thanks in advance,
Thank you guys Big time. Am sorted.
Greate articale, very helpful.
Good article. However, most applications will not be connecting to the server as SA. To send mail using sp_send_dbmail the account being used must be a member of DatabaseMailUserRole, Most application accounts will not be so if you have a stored procedure in anotherdatabase that wants to execute sp_send_dbmail you will get an error.
As yet I am not sure what the bet solution for this is. ie create a sendmail role and allow the sql accounts to be members of this and allow this role to execute sp_send_dbmail
Any one got any better ideas ?
Hello Sir,
I lost some record from tables and I don’t have any backup.
Is it possible to retrive that data? How? Please help me.
Sir
I am also unable to receive email on the address that i specified there . Can you Plz provide help ?
Hi Dave,
Awesome directions on setting up SQL Server Emails. Followed step by step instructions and now my email’s are working.
Thanks
Jeff
U r g8
Hi Dave
i would like to Thank you for getting know a new concept
But i got error while send mail after successfully finished 2 steps
Please let me know and tell me when we have to use database mail(give me a real time example )
once again thanks
The err msg
Msg 8145, Level 16, State 1, Procedure sp_send_dbmail, Line 0
@profilename is not a parameter for procedure sp_send_dbmail.
Wow nice article it is. It will be useful to all.
Thanks so much for this wonderful article!!! Its a very good explanation for whole setup with diagrams.
I learn’t a new thing in SQL Server today. Once again thanks to Pinal and Monica.
Can I configure Database Mail to work with a hotmail acclount ? Can you explain how to configure it, please ?
Hi Pinal
I would like to say thanx for providing detail infomation. It works great for me
Dear Pinal,
I am using MS Reporting Service 2008.
After excuting the report I want that the report should be
send to his supirior at that moment using as user email id to
his superior to suerior adderess in SSRS only.
Very difficult facing please give some idea or hint to solve this problem.
Thanks in Advance
Ramesh Sahoo
I too am facing same issue with SQL 2008. It seems the account works from application using SMTP account, but not in SQL 2008? Can any one give complete details for SQL 2008.
Thanks a lot!
Hi,
I am getting different kind of error in sending database mail from SQL server 2005 which is not included in troubleshooting database mail.
I am getting the error as per the log file
“Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2009-06-01T11:03:23). Exception Message: Cannot send mails to mail server. (Command not implemented. The server response was: Error: command not implemented).
)”
Using the same SMTP server info and same steps I am able to send test mail from different servers. But in a particular server I am not able to send test database mail but I am able to send message through cmd prompt using telnet command.
Please advice any server setup needs to be checked.
Hi all,
Please help me to solve the problem when I send mail by Database Mail, I encounter error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2009-06-04T16:22:26). Exception Message: Could not connect to mail server. (A socket operation was attempted to an unreachable host 209.85.147.109:465). )
I used smtp.gmail.com on 465 port
@Dung Ding Tien.
Please use
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 )
I have demonstrated an example above on the same page, please take a look at it.
~ IM.
@Imran Mohammed
Thank Imran Mohammed,
I also used port 587 as you said but I still got the error message.I did step by step to configure database mail as same as guide in the article and I took your demonstration.
If you can,please explain detail.
Hi Imran Mohammed,
I am using SQL Server 2005, i configured database mail and followed all the steps accordingly but got the following error in log file and unable to get a mail also. Could you please help me.
————————————————————————
The mail could not be sent to the recipients because of the mail
server failure. (Sending Mail using Account 1
(2009-06-10T18:10:07).
Exception Message: Could not connect to mail server.
(No such host is known).
Hi,
Thank you So much for u r efforts. I successfully implemented today.
THanks for u r effort Pinal,Monica,Imran..
Regards,
Hari.
I have setup Database Mail – I can right-click on it and send myself a test email message. I get this message correctly.
However, from a Job, I go to Notifications and select my operator (my own email) and select “When the job completes”. Then I execute this job and I never get an email!
What am I missing?
Thanks
- Gustavo
@Gustavo.
Did you configure your SQL Server Agent to use database mail profile when sending an email.
All Alerts or emails are sent by SQL Server Agent.
Go to SQL Server Agent Properties and select Database mail profile. After you make this change, Restart SQL Server Agent ( Remember. no need to restart SQL Server Service, Just Restart, SQL Server Agent thats it).
And then try again to send email. Should work, In case if it did not, let us know.
~ IM.
Hi Pinal,
Great to see your post.. I was very new to databasemail but reading your blog, now m not…. :)
Thanks !!!
Hello,
I have some problem with MS SQL Server 2005 standard.
I dont use sql mail server service at all. but still in SQL Agent log it says following
Message
[355] The mail system failed to initialize; check configuration settings
Message
[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. —> System.Data.SqlClient.SqlException: profile name is not valid
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
a)
After that it Halt the SQL server and then I need to restart sql server and agent.
@Imran,
Error :
Unable to start mail session
profile name is not valid
Please check SQL Server Agent properties. And see if SQL Server Agent is using either database mail or SQL Mail.
If SQL Server Agent says it is using database mail, then it will also show you what is the name that account is using
Then, go to database mail ( in SSMS, Object Explorer), click configure and check if that profile exists ( Some one might have deleted that profile from Database mail.
If SQL Server Agent uses SQL Mail, then check in OUT
If you are sure you are not using it, then go to Surface Area Configuration anc uncheck Database mail, saying SQL Server not to start this service when sql server instance starts.
Its just a wild guess.
~ IM.
Thanks for the article! I’ve referred to it several times. I was wondering… How would I grant someone permission to View Database Mail Log? So far research has said that I need to grant the role securityadmin, but there’s got to be a better way.
Every time i try to send a test mail it show me the following error..
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-07-22T13:26:53). Exception Message: Could not connect to mail server. (No such host is known). )
plese let me know if i m wrong some where.
i have provided followind details :
E-mail address: sql@adveture-works.com
Display name: SQL account
Reply e-mail : sql@adventure-works.com
server name: mail.adventure-works.com
hi pinal,
i have configured the db mail & operator to send notification when job fails.
but i am not getting any notifications.
I am using sql2008
Thanks
Pragnesh
@Pragnesh,
After you configure database mail. You should change SQL Server Agent Properties to use this new database mail profile you just created.
After making a change, Restart only SQL Server Agent.
Also, Check Database mail logs (Right Click Database Mail, click View logs) to fnd out what is the issue.
~ IM.
Inmran Thanks for quick reply.
I have configured as you told.
& it also shows notification email in the log.
but i did not received any email.
Pragnesh
Pinal, nice example of the mail configuration.
Is this working also with an Microsoft exchange server ?
Which is the configuration for this ?
Thanks in advance,
Kurt
Excellent article, i tried this working fine (tried in SQL Server 2008 Standard Edition).
1. I created a auto backup job.
2. Created a alert and operator.
Now i am receiving schedule mail
Thank you very much, saved a lot of time.
Kumar
Sir,
I have a job in sql server 2005 which executes at 11:00 am everyday. It executes a query and sends the query result to users.
Since the server is in a remote location, sometimes we are unable to get connection to the same.
If we are disconnected from the server at around 11:00 am, or for any other reason (powerfailure) the server shutsdown, how can we get the email on the next restart of the server?
Thanks a lot in advance for your kind help !
Hi Pinal & All
I m using sql server 2005 and i used the db mail facility of the sql server. i want to do auto mail means job to the employee for the wish of birthday for that i am using cursor but it doesnt work..
so how to use cursor for fetch the one by one email id of employee and send the mail to them..
if there are many employee born on same date then how can i send wish mail to all with the help of cursor and cursor is not working in job schedule pls help me…
in job it is use msdb sp like
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’test’,
@recipients=’dhavalsanghvi2006@gmail.com’,
@subject=’Test message’,
@body=’This is the body of the test message.
Congrates Database Mail Received By you Successfully.’
above code is working in job schedule but when i m use of cursor id doesnt work..
Thanks,
Dhaval
when i use the @query option database mail fails to send the email, if i quote it it works just fine.
Is there a way to use it fine?
or just work arounds like generating strings outside the email and later merge them in the @body option (which i dont want to).
note: using SQL2008, i know query works in a separate process, double single quote marks used, query was tested and works fine, @execute_query_database used to relate schema
@Boxek
What is your question ?
Is there a way to use it fine ?
This does not makes any sense to me.
You already know a solution, When you dont want to use it, please specify why you dont want to use a solution and what is that you want us to do.
Please help us to help you by providing complete information.
~ IM.
Sorry, my english might be not good, hehe. I meant, that i followed Pinal’s tutorial and everything worked out just fine, to send mail with database mail.
But, if i use something like
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.’
–this is the problematic part–
@query = ‘SELECT * From sysobjects’;
@attach_query_result_as_file = 1,
@query_attachment_filename =’Results.txt’;
It shows an error (i cant remember the exact sentence, but i’ll write again when im in my workstation). My question is: Why if i use @query option i get an error (the sentence works) and it doesn’t show error if i dont use that option.
Sorry i had some misspellings in the code (in the comment, not in my test source). I’ll write it again:
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.’,
- – this is the problematic part
@query = ‘SELECT * From sysobjects’,
@attach_query_result_as_file = 1,
@query_attachment_filename =’Results.txt’;
Thank you for the nice overview.
I would also recommend that you also add some instructions and screenshots showing how to configure SQL Server Agents to use Database Mail.
Microsoft’s article cover it here: http://msdn.microsoft.com/en-us/library/ms190437(SQL.90).aspx
Enjoy.
Thank you very much to every one
I learned a lot today
Hi Pinal
When i Right click on the database mail i am not able to see the option to configure database mail please help me
Hi Imran & Pinal,
I go the Database Mail setup correctly and able to send test mails to my email id.
Now my question is in real time i hava a scenario it works like this.
Every day @10Pm i have to send an email to users.
That email will consists of results for 5 columns from different tables using joins.
I like to know how to do this step by step.
Do i have to create this using trigger, or stored procedure or jobs or alerts.I’m totally confused.
and this is very urgent for me.
Please send me the reply asap.
@srinivas reddy
Read this article,
http://articles.techrepublic.com.com/5100-10878_11-6164310.html#
Read : query result section.
Step 1: Write a sql statement, to send email, put you select statement in the same query and send the output of the query in the email.
Step2: Now put this script in a job as one of the job step and then schedule this job to run at 10 PM.
~ IM.
Thanks Imran..it worked for me.
Thansk a lot.
Hi Pinal,
I want to send email when the SSIS Package fails due to any reason. If you know how achive this task then please let me know asap.
Thanks in advance
Pratik
@Pratik,
Execute SSIS Package as a job, and schedule that job.
And set a notification to that job, like if job fails then send an alert. Meaning when SSIS Packege will fail, job will fail and if job fails then you will get a notification email.
~ IM.
Dear, its very very very helpful, now i can send some automated e-mail through sqlserver.
but now my problem is i want to send the result of the qeury, i don’t want to send plain, please let me know, how can i send the data which will be the result of my Select Statement.
example.
select buyername, address, credilimit from emp
where creditlimit > 25,000.
@RamZan
Read this article,
http://articles.techrepublic.com.com/5100-10878_11-6164310.html#
Read : query result section.
Step 1: Write a sql statement, to send email, put you select statement in the same query and send the output of the query in the email.
~ IM.
Hi all, I have configured database mail and it is working well.
My query is is there any possibility to configure database mail on SQL server 2008 Express Edition.
Thanks
Hey Pinal,
It’s indeed a greate articale.
Good efforts, thanks a ton.
Ninad
MCTS
Hey Pinal,
i have two consecutive statements in the same procedure to send mail . it was working for almost 2years. all of a sudden we are facing a problem. some time only first mail is going through. second mail fails with the reason …..Relaying not allowed”. what went wrong? How do I solve this issue?. any help is appreciated
priya
Hi Pinal,
I am adding these to the above post
I am using Sql server 2005,
I am sending mails using EXEC msdb.dbo.sp_send_dbmail
we don’t have Exchange server. once we configured the database mail, is it using any SMTP sever?. could you clarify how the mail works?
we are using the third party email server, Network solutions for our office mails.
where do I configure the “Relaying”.. We don’t have IIS in the sql server machine
The mail works sometime , but sometimes it fails. Is this a network issue?
i did all the configurations for gmail it did not work…
but works fine for mail company id and server name and with given port number
answers:
priya:
yes, sql it is sending using an SMTP server; this is configured when creating the mail profile along with the accounts; these accounts are “SMTP accounts”, that means these they are used to send the email
katheesh kumar:
mail sending is restricted to local company accounts; in order to send email to outside you have to add the exceptions (external mail addresses) or make an automatic relay to forward the emails form the sql server
@katheesh kumar
STMP uses a port to send data from your computer to mail server. Just like SQL Server uses port 1433 (default).
STMP for gmail server is 587.
Default port for SMTP is 25. Usually in many companies they block all port that are not needed. It could be possible that port 25 has been unblocked and that is why you could send email through your office mail server and port 587 is blocked that is why you cannot send email using gmail server.
Solution, make exception in Firewall to unblock 587 Or Turn off firewall and test sending an email using gmail mail server, if it goes through. Then it is confirmed that problem is with Firewall blocking. Don’t forget to turn on your Firewall after testing is completed.
~ IM.
@Octavian
Thanks for the reply.
Do you have any idea why the email is not going through some time?. It works one day , next day it fails. we have a bunch of jobs and each send emails. some of the mails have no problem but some have . this Database mail has been working for about 2 years without any issues. all of a sudden this issue happened. (Relaying not allowed error..) We have not changed anything on the SQl server side. Do you think it is the problem with third party mail server?