Post reply

email query results as csv - How to avoid the dashes after the header row.

  • April 19, 2016 at 5:31 am

    #318416

    Hi guys,

    Please help to avoid the hyphens in the second row after the header row. I use the query below.

    Also how can I change this format to save as excel.

    DECLARE @dl CHAR(1) = CHAR(9);

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Profile1',

    @recipients = 'xxxxxx@xxx.com',

    @execute_query_database= 'Mydb',

    @query = @sqlquery,

    @query_attachment_filename=results.csv,

    @query_result_no_padding=1,

    @query_result_separator=@DL,

    @subject = @subject1,

    @body=@body,

    @attach_query_result_as_file = 1 ;

    csv output:

    Column1Column2Column3

    ------------------------------------

    1abc123

    2ads234

    I wanted the output like this:

    Column1Column2Column3

    1abc123

    2ads234

  • April 19, 2016 at 8:22 am

    #1873084

    One way to do it(not pretty) would be to suppress the column headers with the @query_result_header then add a rows with headers as part of your query. you will have issues with non-string datatypes.

    DECLARE @dl CHAR(1) = CHAR(9),

    @qry nvarchar(max) = 'select ''name'',''compatibility_level'' union all select name,cast(compatibility_level as varchar(3)) from sys.databases'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'profile',

    @recipients = 'email@domain.com',

    @execute_query_database= 'master',

    @query = @qry,

    @query_attachment_filename='results.csv',

    @query_result_header = 0,

    @query_result_no_padding=1,

    @query_result_separator=@DL,

    @subject = 'Test',

    @body='Test',

    @attach_query_result_as_file = 1 ;

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • April 19, 2016 at 9:26 am

    #1873102

    Thanks Robert

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply