Post reply

SQL Export to CSV Problem

  • October 11, 2016 at 6:38 pm

    #331208

    I am using the following code to mail out a CSV file. When total number of characters in a row is more than 250(or so), the text was moved to the next row. How can I fit it in with more than 250-character in my current process?

    declare@content varchar(500)

    set@content = 'Is the name of a table and column from which to read. Table and column names must comply with the rules for identifiers.Table and column names must comply with the rules for identifiers.Table and column names must comply. Add more characters'

    Declare @tabSRP char(1) = CHAR(9)

    Declare @sqlData varchar(500)

    Set @sqlData = N'Set Nocount On SELECT ''' + @content + ''' as [Col-1], ''User_Name'' as [Col-2], getDate() as [Col-3]'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'EmailProfile',

    @recipients = 'myemail@aol.com',

    @subject = 'Test Truncation',

    @body = 'test.',

    @query = @sqlData,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'Test.csv',

    @query_result_separator=@tabSRP,

    @query_result_no_padding = 1

  • October 11, 2016 at 7:39 pm

    #1906012

    Not clear what the problem is here, can you elaborate further?

    😎

  • October 11, 2016 at 9:20 pm

    #1906015

    jay-125866 (10/11/2016)

    I am using the following code to mail out a CSV file. When total number of characters in a row is more than 250(or so), the text was moved to the next row. How can I fit it in with more than 250-character in my current process?

    declare@content varchar(500)

    set@content = 'Is the name of a table and column from which to read. Table and column names must comply with the rules for identifiers.Table and column names must comply with the rules for identifiers.Table and column names must comply. Add more characters'

    Declare @tabSRP char(1) = CHAR(9)

    Declare @sqlData varchar(500)

    Set @sqlData = N'Set Nocount On SELECT ''' + @content + ''' as [Col-1], ''User_Name'' as [Col-2], getDate() as [Col-3]'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'EmailProfile',

    @recipients = 'myemail@aol.com',

    @subject = 'Test Truncation',

    @body = 'test.',

    @query = @sqlData,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'Test.csv',

    @query_result_separator=@tabSRP,

    @query_result_no_padding = 1

    Heh... "Must look eye..." The answer you seek is in the Books Online documentation for sp_send_dbmail. Google search is as follows...

    https://www.google.com/?gws_rd=ssl#q=sp_send_dbmail

    The part you're looking for has been quoted below.

    [ @query_result_width = ] query_result_width

    Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, [font="Arial Black"]with a default of 256[/font]. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 12, 2016 at 6:26 am

    #1906090

    Thanks, Jeff!

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

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