sp_send_dbmail

• pmadhavapeddi22

  SSCertifiable

  Points: 5306

  More actions

  April 25, 2012 at 12:07 am

  #149942

  Hi

  when I am using @query_no_truncate=1 in sp_send_dbmail

  I am not getting query header in the attached files.

  As per msdn, if we use @query_no_truncate=1 then, column headers will not be displayed.

  But I dont want query to be truncated and at the same time I want column headers in the attached file.

  What is the work around for this ?

  Your help is appreciated

  Thanks in advance

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  April 25, 2012 at 1:46 pm

  #1479320

  You would need to use a UNION ALL to generate a row containing your column headers, like this:

  EXEC msdb.dbo.sp_send_dbmail

  @recipients = 'OPC@DOMAIN.COM',

  @subject = N'test',

  @body = N'test',

  @query = N'SET NOCOUNT ON;

  SELECT x.Name

  FROM (

  SELECT ''Name'' AS name,

  1 AS OrderColumn

  UNION ALL

  SELECT ''-----------------------------'' AS name,

  2 AS OrderColumn

  UNION ALL

  SELECT name,

  3 AS OrderColumn

  FROM sys.tables

  ) x

  ORDER BY x.OrderColumn',

  @execute_query_database = 'master',

  @attach_query_result_as_file = 1,

  @query_result_width = 500,

  @query_no_truncate = 1;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato