SQL Send Email - Best solution.

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  April 27, 2015 at 12:54 pm

  #317799

  Hello

  I wanted to implement the feature to send the email in HTML format from SQL. Which option will be more easy and less resource consumption? Like

  sp_send_dbmail or sp_OAMethod or anything else?

  Please provide the suggestion. Thanks!!!

  Thanks

  Shuaib

• ninad.soman0

  Ten Centuries

  Points: 1111

  More actions

  April 28, 2015 at 6:33 am

  #1793907

  Hi,

  You could use powershell as well ?

  Not sure what all you want to be sent in the email; but if its something like disk space usage etc, then you can achieve that using PS. That way you don't need to enable the feature on SQL, and can have the PS script running from a different server altogether.

  There are options where you can include nice HTML formatting in the PS code.

  Thank you,

  Ninad

• Jeff Moden

  SSC Guru

  Points: 1004407

  More actions

  April 28, 2015 at 8:15 am

  #1793946

  ShuaibV (4/27/2015)

  ---

  Hello

  I wanted to implement the feature to send the email in HTML format from SQL. Which option will be more easy and less resource consumption? Like

  sp_send_dbmail or sp_OAMethod or anything else?

  Please provide the suggestion. Thanks!!!

  Thanks

  Shuaib

  sp_Send_DBMail works just fine. And, to be honest, I wouldn't increase the complexity of this by using PoSh. If you use the sp_OAMethod (which involves the use of CDONTS, which also works fine), you can avoid setting up sp_Send_DBMail but then you're just trading one feature for another insofar as surface area goes.

  sp_send_dbmail requires users to have membership in the DatabaseMailUser database role in the msdb database. The sp_OA* methods require SysAdmin privs. I never give either to users or groups. Instead, I write stored procedures for the tasks at hand and grant privs to the stored procedures (usually by user group) and use EXECUTE AS OWNER in the stored procedures.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)