none
sp_send_dbmail formatting results - one column has line breaks and wraps a long text string

    Question

  • I have a procedure that returns several results.  One of which has a column with a long string, but I am trimming it to 300 characters.

    Regardless, when the email is received, the attached file contains the query results.

    The one resultset with the long strings doesnt look right.  It is wrapping within the column, and this throws off the other columns and their headers.
    I want it all on one line, no wrapping.
    How can I force that one column not to wrap its text

    What setting or parameter value should I look at?

    USE msdb
        EXEC sp_send_dbmail
          @profile_name = 'DevSQL',
          @recipients = 'me@somewhere.com',
          @subject = 'my report',
          @body = 'my report',
          @query_result_header=1,
          @execute_query_database = 'DBdev',
          @attach_query_result_as_file = 1,
          @query_attachment_filename = 'MyReport.rpt',
          @query_result_width = 4000,     
          @query = 'EXEC Myprocedure'

    Wednesday, July 24, 2013 7:53 PM

Answers

  • I managed to figure it out.  The values that had the line wrapping, I just had to use REPLACE a few times, once with CHAR(10) and then for CHAR(13) and replace those with ''

    • Marked as answer by shiftbit Wednesday, July 24, 2013 8:34 PM
    Wednesday, July 24, 2013 8:34 PM