CSV file

• Shahin

  Mr or Mrs. 500

  Points: 538

  More actions

  November 23, 2015 at 5:33 am

  #150418

  HI,

  we have a query that must run on an scheduled time and send the result by DBmail,

  the result of the query has some columns that I would like to show nicly in the body of the mail or as an atteched file:

  I did run this query

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'test profile',

  @recipients = 'shahin@gmail.com',

  @subject = 'Database Mail Test by T-SQL',

  @execute_query_database = tms,

  @body = 'This is a test e-mail sent from Database Mail on.',

  @query ='

  SELECT ''COllectie'' AS Collectie, Users.Login, Users.DisplayName, SecurityGroups.SecurityGroup

  FROM Users INNER JOIN

  SecurityGroups ON Users.SecurityGroupID = SecurityGroups.SecurityGroupID

  WHERE (NOT (Users.Login = N''Administrator''))

  order by login;',

  @body_format = 'HTML',

  @query_attachment_filename = 'old.csv',

  @query_result_separator = ' ',

  @attach_query_result_as_file = 0,

  @query_result_no_padding= 1,

  @query_result_width = 9999,

  @exclude_query_output =1,

  @append_query_error = 0,

  @query_result_header =1;

  when I open the attechment it shows no row (see the attachment please)

• Shahin

  Mr or Mrs. 500

  Points: 538

  More actions

  November 23, 2015 at 6:33 am

  #1841684

  Hi,

  I find a soltion to this question I did add this to the query:

  DECLARE @tab char(1) = CHAR(9)

  @query_result_separator = @tab

  now I get an CSV file with columens and row but atill I have to still ajust the coluemns to see the data and I think this cannot be change becuase this is defualt width seting of CSV.

  how can show columns and row in the body of the email?

  Thanks