Posted By

sankarwaits on 04/07/17


Tagged

sql mail


Versions (?)

Sending Mail in MS SQL


 / Published in: SQL
 

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

  1. --Setting up the Mail account and profile for sending email from SQL Server
  2. --Creating a Profile
  3. Exec msdb.dbo.sysmail_add_profile_sp
  4. @profile_name = 'ProfileName' ,
  5. @description = 'description'
  6.  
  7. -- Create a Mail account for SMTP Server.
  8. EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'AccountName',
  9. @email_address = [email protected]' ,
  10. @display_name = 'DisplayName' ,
  11. @replyto_address = [email protected]' ,
  12. @description = 'description',
  13. @mailserver_name = 'smtpserveraddress ' ,
  14. @mailserver_type = 'SMTP' ,
  15. @use_default_credentials = 0
  16.  
  17. -- Adding the account to the profile
  18. EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
  19. @profile_name = 'ProfileName',
  20. @account_name = 'AccountName',
  21. @sequence_number =1 ;
  22.  
  23. -- Granting access to the profile to the DatabaseMailUserRole of MSDB
  24. EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
  25. @profile_name = 'ProfileName',
  26. @principal_id = 0,
  27. @is_default = 1 ;
  28.  
  29. --Sending Test Mail
  30. EXEC msdb.dbo.sp_send_dbmail
  31. @profile_name = 'ProfileName',
  32. @recipients = [email protected]',
  33. @body = 'Database Mail Testing...',
  34. @subject = 'Database Mail from SQL Server';
  35.  
  36. --To check the mail status
  37. SELECT * FROM msdb.dbo.sysmail_allitems
  38.  
  39.  
  40. --Mails sent on same day
  41. SELECT * FROM msdb.dbo.sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())
  42.  
  43. --mails failed on same day
  44. SELECT items.subject ,
  45. items.recipients ,
  46. items.copy_recipients ,
  47. items.blind_copy_recipients ,
  48. items.last_mod_date
  49. -- l.description
  50. FROM msdb.dbo.sysmail_faileditems AS items
  51. LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l
  52. ON items.mailitem_id = l.mailitem_id
  53. WHERE items.last_mod_date > DATEADD(DAY, -1,GETDATE())
  54.  
  55.  
  56. --Tables involved for Mail settings and sending
  57.  
  58. SELECT * FROM msdb.dbo.Sysmail_server
  59. SELECT * FROM msdb.dbo.sysmail_account
  60. SELECT * FROM msdb.dbo.sysmail_profile
  61. SELECT * FROM msdb.dbo.Sysmail_allitems
  62. SELECT * FROM msdb.dbo.Sysmail_log
  63. SELECT * FROM msdb.dbo.Sysmail_configuration

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: collegesniper on April 28, 2017

Here include program describe about the configure email account and this email will be send to the sql server.all informations are described simply in this program.easily understand everyone.thanku so much for your post. job oriented diploma courses

You need to login to post a comment.