1

This question is similar to several questions I've found where Line Feeds or carriage returns cause copying SQL grid results to Excel to have Line Splitting issues and your results will be cut off and started on the next row.

Name     Order#    Date        PartDescription     Store      Phone#
----     -------   ------      ----------------    -----      -----------
Tom's    3181      10/7/2017   SB4356 Artwork...   Downtown   208.452.6354

Will instead look like this

Name     Order#    Date        PartDescription     Store      Phone#
----     -------   ------      ----------------    -----      -----------
Tom's    3181      10/7/2017   SB4356 Artwork...   Downt   
own      2080.452.6354

However, this problem does not occur when I copy the SQL grid results to Excel. This problem occurs for me when I run the SQL as dynamic sql and email the results to myself via CSV. I'm not sure what the difference here is. The issue can be isolated to the Part Description field, its usually 100+ user-entered characters. If I remove that field this doesn't happen.

I've tried doing:

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE( PartDescription, CHAR(13), ''), CHAR(10), ' '), CHAR(9), ' '), ',', ' ')))

and tried it in smaller steps and it hasn't had any effect. Like I said, it works copying the results to Excel. Just not when I email it to CSV. I'm not really sure how to isolate what inivisible charatcers might be causing this (if it is that).

I've used this method to fix others auto-reports in the past. This one is being stubborn. The SQL runs dynamically in SQL Server Agent, which then emails the results out in a csv format. I also tried saving the SQL in a view and called the view in dynamic SQL just for fun, no change.

BWoods
  • 110
  • 1
  • 9
  • Does "copy the SQL results to Excel" mean that you copy out of the SSMS grid? No one can really pose an answer to this - it's pretty vague. Can you isolate a piece of data with linefeeds that goes through your expression but still appears with linefeeds in an email? – Nick.Mc Nov 08 '17 at 03:20
  • Copying does refer to Copy/Pasting from the SSMS grid. I'm not sure how to make it more descriptive when I don't fully understand what the problem is. I'll update the question with an isolated data row to try and better illustrate the issue – BWoods Nov 08 '17 at 17:49

1 Answers1

2

Had the same issue, turns out it was line wrapping when it hit 255 characters. I'm thinking you may have the same issue if your PartDescription field has many characters. Assuming you are using sp_send_dbmail, if you add the option:

@query_result_width=500

or whatever width you require, it should fix it.

Martin Boros
  • 780
  • 3
  • 9
  • 22
  • 1
    I'll have to try it! May or may not fix the issue but it's more than I've been able to find otherwise. – BWoods Jan 10 '18 at 18:32