May 6, 2008 at 2:57 am
Hi there!
I want to send an email by the SQL Server to an Outlook account when a scheduled job finishes with success or with errors. Is there any system stored procedures I can use or do I have to create one? Do I need the DataBase Mail and how can I use it?
Thanks for any help!!
May 6, 2008 at 3:09 am
The easiest way is to set up database mail, use the following script:-
/*==========================================================================
-- Purpose: Used to create Database Mail in SQL Server 2005
-- Author:Carolyn Richardson
-- Date:24/09/2007
-- ***Important***
--Alter the ServerName to make it clear re which server is sending the mail (search and replace the test ServerName), alter the mail server name and alter the email address to test it works:-
==========================================================================*/
--Reconfigure the server to allow email
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
reconfigure
go
/*Add Service Account Permission
As an extra security feature SQL Server 2005 uses a role with specific permissions in the MSDB database, not even system administrators can use the mail feature without being a member of this role. So you need to either add the specific account that runs SQL Server to this role, or add all administers:-
*/
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'ServerNameMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'ServerName@whatever.co.uk',
@display_name = 'ServerNameMailAccount',
@use_default_credentials = 0,
@mailserver_name = 'mail.server.net' --Alter here
/*
If you look at the propertities you may want to altert the anonymous login to SQL Server authentication.
Create Mail Profile
The next component of the configuration requires the creation of a Mail profile.
We are going to create "ServerNameMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
For help on options use sp_helptext sysmail_add_profile_sp
*/
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'ServerNameMailProfile',
@description = 'Profile used for database mail'
/*
Add Account Profile
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account usding the Database Mail profile you created in last step
For help on options use sp_helptext sysmail_add_profileaccount_sp
*/
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'ServerNameMailProfile',
@account_name = 'ServerNameMailAccount',
@sequence_number = 1
/*Set default Profile
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
For help on options use sysmail_add_principalprofile_sp
*/
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'ServerNameMailProfile',
@principal_name = 'public',
@is_default = 1 ;
/*To send a test email from SQL Server. Execute the statement below.*/
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='SQLAlerts@whatever.co.uk',--Email Address change here
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;
--***********************************************************
You will also need to alter the properties of SQL Agent - Alert system tab to ensure that Enable Mail Profile is checked.
May 6, 2008 at 3:32 am
thanks for the reply.
the message is queued but it did'n arrived. I also want to associate the message to a sheduled job.
May 6, 2008 at 4:39 am
In Management Studio under Management/Database Mail right click and see if you can send a test email. If not, check you have entered the correct mailserver_name.
In SQL Server Agent add an operator.
On the job properties under notifications add the operator.
August 3, 2009 at 7:34 pm
I am having the same issue, according to the database mail error log, I get the following message :
'The mail could not be sent to recipients because of the mail server failure. [Sending Mail using Account1. Exception Message: cannot send mails to mail server [syntax error, command unrecogized. The Server response was: CLIENT AUTHENTICATION REQUIRED. USE ESTMP EHLO AND AUTH.].]'
I set my mail server up as smtp.AOL.com using port 587. I've also tried port 25. In both cases, I tried Authentication checked and not checked. Thinking this must be an issue with AOL, Ive tried a mail account at Yahoo, gmail and school mail (myAcct@ccsf.edu)- and I get the same error.
Has anybody successfully recieved mail/alerts at a 3rd party ISP sent from SQL Server 2005 Database Mail? I tried both Workstation and Enterprise servers and can't get it to work :w00t: I just want to send a simple message that a job completed.
For those getting started... you might perform a Database Mail email test before setting anything else up, such as Alerts/Operators/JobScheduling, etc...
August 8, 2009 at 12:18 pm
I set my mail server up as smtp.AOL.com using port 587. I've also tried port 25. In both cases, I tried Authentication checked and not checked. Thinking this must be an issue with AOL, Ive tried a mail account at Yahoo, gmail and school mail (myAcct@ccsf.edu)- and I get the same error.
You can get mail from GMail or Yahoo account but you need Asp.net code with SMTP service like IIS or Exchange running to do transport because there is no way for SQL Server to communicate with GMail or Yahoo mail. In Asp.net you have to add a Web.config section with the address of your transport.
Kind regards,
Gift Peddie
August 8, 2009 at 12:55 pm
Thank you for taking the time to get back to me. I have IIS installed on my XP workstation with SMTP turned on, and I am fairly certain that the production/work server has IIS turned on too since it is getting input from another server outside the company firewall... but when it comes to ASP.net I'm clueless... can you provide me with an example, assuming my email address was tll414@gmail.com, of what I would have to add to the web.config file or point me to a link? Also, I've read somewhere when config Database Mail for gmail, I think I have to use port 587 instead of port 25. Since I never got it working (I've tried both)... I don't know if that is a fact or not.
Thanks again!
August 8, 2009 at 8:29 pm
Here is the Asp.net code I have not seen using SQL Server if I find such code I will post again.
http://www.aspdotnetfaq.com/Faq/How-to-send-HTML-Email-from-ASP-NET-using-your-Gmail-account.aspx
Hi I just found a step by step walk through from Microsoft.
Kind regards,
Gift Peddie
August 8, 2009 at 9:29 pm
Gift Peddie,
Thanks again for getting back to me... the MS link for setting up Database Mail is a good article, I've done it over and over, with and without SSL, Ports 587, 25 deleted config and still can't send testmail, etc. - but thank you for the link.
I work on the backend server so I'll have my coworker who works on the frontend server take a look at the ASP.net code and see if we can get it working. He would have a better idea than I to set it up. I'll keep you posted!
August 9, 2009 at 10:33 am
That may be related to either firewall or you maybe using Windows authentication instead of Basic authentication. So check with your system admin if you are the system admin check security.
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply