Posted By

sankarwaits on 04/07/17

Tagged

sql
mail

Versions (?)

Last Edited at 04/07/17 03:04pm

Statistics

Viewed 1057 times

Favorited by 0 user(s)

Related snippets

Sending Mail in MS SQL

/ Published in: SQL

Following steps to be completed to configure an email account and send email from SQL Server

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

--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 =  '[email protected]' ,  
		@display_name =  'DisplayName' ,   
		@replyto_address =  '[email protected]' ,   
		@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 = '[email protected]',
		@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