Sending query result as CSV file using DatabaseMail - problem with header

jposp
jposp used Ask the Experts™
on
I want to attach as CSV query output, so i wrote something like that:


EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'ReportsSQL',
@recipients= 'mail@gmail.com',
@subject='Report',
@query ='SET NOCOUNT ON;SELECT login, name, city FROM users;SET NOCOUNT ON;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'output.csv',
@query_result_no_padding = 1,
@append_query_error = 0,
@exclude_query_output = 1,
@query_result_header = 1,
@query_result_separator=';'

Output CSV file seems to be allright with one exception - secodn line:

login;name;city
-----;----;----
alex;Alexander;New York
misza;Misza;Moscow

Open in new window


How to get ridd of the second line with dashes ???
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project Leader
Commented:
Hello,

If you use "@query_result_header = 1", then the line with dashes cannot be eliminated.
A work around is to explicity add a header row in your SELECT command with a UNION ALL, and then set "@query_result_header = 0"

For example:

EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'ReportsSQL',
@recipients= 'mail@gmail.com',
@subject='Report',
@query ="SET NOCOUNT ON;
      SELECT 'login' as login, 'name' as name, 'city' as city
      union all
      SELECT login, name, city FROM users;SET NOCOUNT ON;",
@attach_query_result_as_file = 1,
@query_attachment_filename = 'output.csv',
@query_result_no_padding = 1,
@append_query_error = 0,
@exclude_query_output = 1,
@query_result_header = 0,
@query_result_separator=';'

Thanks,
Harish

Author

Commented:
@harish_varghese - solution works for me :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial