Stop query output to CSV wrapping lines at 255 characters

Question

I'm creating a task to send an email with a SQL output as a csv file attached in the SQL Server Agent. Normally not a problem, my code looks like this:

declare @tab char(1) = char(9)
EXEC  msdb.dbo.sp_send_dbmail
      @profile_name = 'MailProfile',
      @recipients = 'email@email.com', 
      @subject = 'TheSubject',
      @body = 'TheBody',
      @query = 'select * from ##TempTableBeingUsed',      
      @Attach_Query_result_as_file = 1,
      @query_attachment_filename = 'report.csv',
      @query_result_separator = @tab,
      @query_result_no_padding=1,
      @exclude_query_output=0,
      @append_query_error =0,
      @query_result_header=1

And this normally works, but with the current query the column names are line wrapping, and a lot of the data rows are line wrapping, without a line break being present. It looks like it does this whenever the length of the row is more then 255 characters. Is there any way to bypass this? Looks it may be the same as this issue, SQL Email to CSV, Results have Line Splitting issues.

`sp_send_dbmail` is meant to sent messages to administrators, not export nicely formatted data and send it to users. That's the job of Reporting Services which *does* support subscritpions, scheduling, sending to emails — Panagiotis Kanavos, Jan 05 '18 at 17:39

score 11 · Accepted Answer · answered Jan 05 '18 at 17:51

11

Adding the option:

@query_result_width=500

fixed it

answered Jan 05 '18 at 17:51

Martin Boros

780
3
9
22

Stop query output to CSV wrapping lines at 255 characters

1 Answers1