DBmail with formatted Query results in Body

• Black Mamba

  Right there with Babe

  Points: 786

  More actions

  July 16, 2010 at 7:18 pm

  #224164

  Hi,

  I need some help setting up this query. I Run this code to send results of a table via dbmail

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=N'myname@domain.com',

  @body='This is an auto-generated mail to show Employee Number, Employee Department Code and Employee Name',

  @subject ='New Employee No & Name,

  @body_format = 'HTML',

  @profile_name ='my_profile',

  @query ='Select * from new_employee_table' ;

  I get an email but the formatting is really not even readable :

  Hi,

  This is an auto-generated mail to show Employee Number, Employee Department Code and Employee Name

  EmployeeNo EmpDept EmpName ----------- --------------- 70588 15 John Doe(1 rows affected)

  The table new_employee_table has 3 columns with just one row. How can I format so that it shows a formatted email preferably as a table : Something like this :-

  Hi ,

  This is an auto-generated mail to show Employee Number, Employee Department Code and Employee Name

  EmployeeNo EmpDept EmpName

  70588 15 John Doe

• Tara Kizer

  SSCommitted

  Points: 1789

  More actions

  July 16, 2010 at 8:11 pm

  #1194447

  You can use HTML in sp_send_dbmail. Check sp_send_dbmail in BOL for details.

  Alternatively, you could put attach the query results, which would probably have better formatting than what you are seeing now.

  Tara Kizer
  Microsoft MVP for Windows Server System - SQL Server
  Ramblings of a DBA (My SQL Server Blog)[/url]
  Subscribe to my blog

• Black Mamba

  Right there with Babe

  Points: 786

  More actions

  July 17, 2010 at 11:23 pm

  #1194607

  Thanks Tara.

  I could use HTML and get teh desired output.:-)