sp_send_dbmail attachment encoding

Question

I am using sp_send_dbmail in SQL2005 to send an email with the results in an attachment. When the attachment is sent it is UCS-2 Encoded, I want it to be ANSI or UTF-8.

Here is the SQL EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected]' , @query = 'DECLARE @string_to_trim varchar(60);SET @string_to_trim = ''1234''; select rtrim(@string_to_trim), ''tom''' , @query_result_header=0 , @subject = 'see attach' , @body= 'temp body' , @profile_name= N'wksql01tAdmin' , @body_format = 'HTML' ,@query_result_separator = ',' ,@query_attachment_filename = 'results.csv' ,@query_no_truncate = '0' ,@attach_query_result_as_file = 1

I have seen some comments on the internet that this is fixed with sql2005 SP2, but do not find it to be the case.

Any ideas?

Answer 1

I think the only way to get around what you are seeing is to use BCP to dump the data to a flat file and then attach that file. Sorry I couldn't be more help. :(

Answer 2

That is ok, thanks for your answer.

That is the type of responses I was getting from searching, just wanted to make sure there wasn't a better way before I did it.