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 ='[email protected]',
@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
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.