Send Email from SQL Server
--Setting up the Mail account and profile for sending email from SQL Server
--Creating a Profile
Exec msdb.dbo.sysmail_add_profile_sp
@profile_name = 'ProfileName' ,
@description = 'description'
-- Create a Mail account for SMTP Server.
EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'AccountName',
@email_address = 'FromEmailAddress@xxx.xxx' ,
@display_name = 'DisplayName' ,
@replyto_address = 'FromEmailAddress@xxx.xxx' ,
@description = 'description',
@mailserver_name = 'smtpserveraddress ' ,
@mailserver_type = 'SMTP' ,
@use_default_credentials = 0
-- Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'ProfileName',
@account_name = 'AccountName',
@sequence_number =1 ;
-- Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'ProfileName',
@principal_id = 0,
@is_default = 1 ;
--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients = 'ToEmailAddress@xxx.xxx',
@body = 'Database Mail Testing...',
@subject = 'Database Mail from SQL Server';
--To check the mail status
SELECT * FROM msdb.dbo.sysmail_allitems
--Mails sent on same day
SELECT * FROM msdb.dbo.sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())
--mails failed on same day
SELECT items.subject ,
items.recipients ,
items.copy_recipients ,
items.blind_copy_recipients ,
items.last_mod_date
-- l.description
FROM msdb.dbo.sysmail_faileditems AS items
LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > DATEADD(DAY, -1,GETDATE())
--Tables involved for Mail settings and sending
/*
SELECT * FROM msdb.dbo.Sysmail_server
SELECT * FROM msdb.dbo.sysmail_account
SELECT * FROM msdb.dbo.sysmail_profile
SELECT * FROM msdb.dbo.Sysmail_allitems
SELECT * FROM msdb.dbo.Sysmail_log
SELECT * FROM msdb.dbo.Sysmail_configuration
*/