Advanced Search

Return to Snippet

Revision: 71978
at April 7, 2017 15:04 by sankarwaits
Initial Code 
--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
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

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

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Sending Mail in MS SQL

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql, mail

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL