Can DatabaseMail attach a formatted Excel file

• sandosh.star

  SSC Veteran

  Points: 281

  More actions

  February 5, 2013 at 9:22 pm

  #288975

  my query

  USE [AP_ECC]

  GO

  /****** Object: StoredProcedure [dbo].[ap_ecc_Mail_for_statuslog] Script Date: 02/06/2013 11:03:01 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER procedure [dbo].[ap_ecc_Mail_for_statuslog] as

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=N'internal@zeroone.com'

  ,@body='Hi Team,

  Please find attached the status log for today.

  Please check the status .

  Regards,

  Reporting team

  '

  ,@subject ='Status Log for Today'

  ,@profile_name ='Status HDS A'

  ,@query ='SELECT [TableName]

  ,[DateLastRun]

  ,[StartTimeStamp]

  ,[EndTimeStamp]

  ,[StatusLastRun]

  FROM [ICMRPTSVR].[ICMRPTDB].[dbo].[icmrptdb_statuslog]'

  ,@attach_query_result_as_file = 1

  ,@query_attachment_filename ='Status_Log.txt'

  my query is attaching txt file i want it as excel type

• Andrew G

  SSChampion

  Points: 12809

  More actions

  February 6, 2013 at 12:21 am

  #1584751

  You need a tab delimited file for excel to recognise it and the file extension needs to be .csv (.xls will work but will display an error first).

  eg.

  DECLARE @query_result_separator CHAR(1) = char(9);

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=N'test@test.com'

  ,@body='Hi Team,

  Please find attached the status log for today.

  Please check the status .

  Regards,

  Reporting team

  '

  ,@subject ='Status Log for Today'

  --,@profile_name ='Status HDS A'

  ,@query ='SELECT ''dddddddddd'' as columnheader, ''aaaaa'' as col2, ''ffffff'' as col3

  UNION ALL

  SELECT ''a'',''b'',''c'''

  ,@attach_query_result_as_file = 1

  ,@query_attachment_filename ='Status_Log.csv'

  ,@query_result_separator= @query_result_separator

  ,@exclude_query_output = 1

• CapnHector

  SSCoach

  Points: 16143

  More actions

  February 6, 2013 at 11:31 am

  #1585076

  foxxo (2/6/2013)

  ---

  You need a tab delimited file for excel to recognise it and the file extension needs to be .csv (.xls will work but will display an error first).

  eg.

  DECLARE @query_result_separator CHAR(1) = char(9);

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=N'test@test.com'

  ,@body='Hi Team,

  Please find attached the status log for today.

  Please check the status .

  Regards,

  Reporting team

  '

  ,@subject ='Status Log for Today'

  --,@profile_name ='Status HDS A'

  ,@query ='SELECT ''dddddddddd'' as columnheader, ''aaaaa'' as col2, ''ffffff'' as col3

  UNION ALL

  SELECT ''a'',''b'',''c'''

  ,@attach_query_result_as_file = 1

  ,@query_attachment_filename ='Status_Log.csv'

  ,@query_result_separator= @query_result_separator

  ,@exclude_query_output = 1

  Please do not make a tab delimited Comma Separated Value file (what csv stands for). Now the suggestion to create a type of file excel can open and display with out being an xls(x) is a very good one. my personal preference is a tab delimited text file (using .txt) or a pipe delimited file as both tabs and pipes are rarely present in data (unlike commas which in a csv file need special handling).

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]