Format the query result in send Email SP

• hamsa_apj

  SSC Eights!

  Points: 825

  More actions

  July 28, 2012 at 3:48 am

  #147291

  _{Hi All,}

  In our project , we trigger emails with csv file as attachment using the SP (msdb.dbo.sp_send_dbmail).Data in the CSV attachment are populated with the query results.

  Could anyone please share how to format the data in the csv attachment. Also, i would like to know whether the formatting the excel data with the below code or do i need to change the approach to implement the email send part. Kindly advise. Thanks in advance.

  Below is the code we used to send the email in SQL Server 2005

  DECLARE @sql

  @sql = 'SELECT FIRST_NAME,LAST_NAME, EMPLOYEE_ID FROM T_EMPLOYEE'

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name ='APP_Admin',

  @recipients = 'asdfdf@yah.com',

  @subject = 'Employee Detail',

  @body_format = 'HTML',

  @body = @email_content,

  @importance = 'Normal',

  @sensitivity = 'Normal',

  @file_attachments = null,

  @query = @sql,

  @attach_query_result_as_file = 1,

  @query_attachment_filename = 'Test.csv',

  @query_result_header = 1,

  @query_result_width = 300,

  @query_result_separator ='',

  @exclude_query_output = 0,

  @append_query_error = 1,

  @query_result_no_padding =1

  -Thanks,

  Hamsa

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  July 28, 2012 at 12:04 pm

  #1518888

  If you want the attachment to be CSV, you need to change

  @query_result_separator ='',

  to be

  @query_result_separator =',',

  Tom