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 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]