Post reply

Issue using sp_send_dbmail for CSV export

  • December 6, 2016 at 3:32 pm

    #319461

    Hello, I am using sp_send_dbmail to create a CSV export. However, for some reason, the header line is "Splitting" into multiple lines upon export. I am trying to figure out what is causing this to happen to no avail as I need my header to stay in the top row.

    Here is my sp_send_dbmail code:

    DECLARE @tab Char(1) = Char(9)

    DECLARE @Subj varchar(Max)= 'Reporting Enclosed!'

    DECLARE @Msg varchar(Max) = 'Reporting period is: ' + CONVERT(varchar(8),@StartDateTime,1) + ' - ' + CONVERT(varchar(8),@EndDateTime,1) + '.' -- for send_dbmail @Body

    DECLARE @Qry varchar(Max) = 'Set NoCount On SELECT * From ##DATA'

    DECLARE @File varchar(Max) = 'Rpt_.csv'

    DECLARE @Recvarchar(Max) = (CASE @DEBUG WHEN 'Y' THEN 'my@email.com' ELSE 'my@email.com;' END)

    EXEC msdb..sp_send_dbmail @Profile_name = 'Reporting',

    @Recipients = @Rec,

    @Subject = @Subj,

    @Body = @Msg,

    @Body_Format = 'Text',

    @Execute_Query_Database = 'myDB',

    @Query = @Qry,

    @Attach_Query_Result_As_File = 1,

    @Query_Attachment_Filename = @File,

    @Query_Result_Separator = @tab,

    @Query_Result_No_Padding = 1

    Here are the results in CSV format:

    Field1Field2Field3Field4Field5Field6Field7Field82016120420161205201612062016120720161208201612092016121020161211201612122016121320161214201612152016121620161217201612182016121920161220201612212016122220161223201612242016122520161226

    2016122720161228201612292016123020161231

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

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

  • December 6, 2016 at 5:21 pm

    #1916386

    there is another parameter, @query_result_width, which i think is a default of 80 or 100, and anything wider wraps, which is what you are seeing

    set it to a large number, like 8000, so your header (and results) don't wrap to the next line.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 7, 2016 at 10:00 am

    #1916564

    Thank you kindly! This solves the problem.:-)

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

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