SQL csv exports cut-off at 256th character

Question

I have stored procedure to send data via email from SQL table as csv-file. Everything else is working, except that csv cuts column's data to 256 characters, even I have a '@query_result_width = 32767'. For some reason that doesn't work. I tried '@query_no_truncate = 1' but that excluded headers and I need to send headers also. Any tips?

 declare @qry nvarchar(max);
    
 Select @qry= 'USE Erve Update FORM Set Instructions = REPLACE(REPLACE(Instructions, CHAR(10), ""),CHAR(9), CHAR(32)) from FORM; select * from FORM'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'NoReply',  
    @recipients ='some@receiver.com', 
    @query = @qry,
    @body = 'Liitteenä kaikki erillisveloitettavat rivit.',
    @subject = 'Erillisveloitettavat rivit',  
    @attach_query_result_as_file = 1 , 
    @query_attachment_filename = 'ErveKaikki-rivit.csv',
    @query_result_separator = ' ',
    @query_result_width = 32767,
    @query_result_no_padding = 1,
    @query_result_header = 1


END

Answer 1

Kinda late to the party, I hope you've fixed your issue by now lol

But for anyone who stumbles on this problem, what worked for me was changing the declaration of my columns (the ones I use in the SELECT query to create my csv output) from nvarchar/varchar(max) to nvarchar/varchar(n), n being a fixed value.

So in OP's case, I'm assuming the columns that are being cut are declared as nvarchar/varchar(max) in the FORM table

Select @qry= 'USE Erve Update FORM Set Instructions = REPLACE(REPLACE(Instructions, CHAR(10), ""),CHAR(9), CHAR(32)) from FORM; select * from FORM'

I use the same code as OP to send the email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile,
    @recipients = @email_to_address,
    @subject = @email_subject,
    @query = @Query,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'filename.csv',
    @query_result_separator = @tab,
    @query_result_no_padding = 1,
    @query_result_width = 32767

Again this worked for me because I was able to change the columns definition and I know for sure that those columns won't have bigger data than 8000 characters.