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?
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.
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 = 'firstname.lastname@example.org', @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 = 'email@example.com', @body = 'Database Mail Testing...', @subject = 'Databas Mail from SQL Server';
--Verifying, check status column
select * from sysmail_allitems