-1

How do I use database mail in SQL Server? I have followed many articles on the forums but I failed with many errors. I read in one book that I must have SMTP server and Microsoft Exchange installed before I can use the database mail? Is this correct?

  • "many errors". Which errors did you get? – Colin 't Hart Apr 20 '15 at 20:07
  • You need to have a running SMTP in order to relay the messages. – DenisT Apr 20 '15 at 20:11
  • Microsoft Exchange is not required, although many organizations use Exchange as their SMTP server. – Dan Guzman Apr 21 '15 at 1:51
6

Yes SMTP server would be good to configure the database mail.

Limitations and Restrictions Enabling SQL Server Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock.

Also,

To configure Database Mail you must be a member of the sysadmin fixed server role. To send Database Mail you must be a member of the DatabaseMailUserRole database role in the msdb database

Just for test, i am using below gmail server as smtp.

Please go through this MSDN article on how to set up as well

Configuring Database Mail Using T-SQL Script

--Enabling Database Mail
sp_configure 'show advanced options', 1
reconfigure

--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLProfile',
@description = 'Mail Service for SQL Server’;

-- Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account',
@email_address = 'youremail@gmail.com',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='youremail',
@password='Emailid password'

-- Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'SQL_Email_Account',
@sequence_number =1 ;

-- Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_id = 0,
@is_default = 1 ;

--Sending Test Mail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = 'youremailid@xxxx.com',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';

--Verifying, check status column

select * from sysmail_allitems
  • sorry for delaying , I was sick, so I did not manage to reply, and thank you for your reply, when I run the above code, I got the following error The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-04-27T05:17:58). Exception Message: Cannot send mails to mail server. (The operation has timed out.). – mohamed Apr 27 '15 at 2:23
  • Please send the screenshot of what you are using in database mail configuration wizard page/ – KASQLDBA Apr 27 '15 at 19:30
0

this is the imagethis is screen shots from the data base mail wizard!

  • the images did not appear correctly do you know how to add image? – mohamed Apr 29 '15 at 22:21
  • Can you give a try with Anonymous authentication? Instead of basic authentication try anonymous and let me know what you see then! – KASQLDBA Apr 30 '15 at 10:40
  • thank you KASQLDBA for your post. I got the same error message. by the way, I have smtp server but I did not configure it because I do not know how? please, tell me how to configure it – mohamed Apr 30 '15 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.