Attachement generated by xp_sendmail

Hello folks, I am sending a email from sql server that consists of results of a query and that's working perfect.
When I open the text file generated by xp_sendmail in notepad it's OK, but when I open it in MS Word or WordPad, I see garbage characters in form of squares added to the content. Those sqare are unicode spaces equivalent to Nchar(140) or Nchar(0020) in SQL. I tried to replace them using the replace function :

declare @.foo varchar(8000)
set @.foo='select top 4 Replace(priMarykey,Nchar(0020),'') as primarykey,
Replace(Sitename,Nchar(0020),'') as Sitename,
Replace(REgNumber,Nchar(0020),'') as REgNumber,
Replace(ContactEmpFirstNames,Nchar(0020),'') as ContactEmp
from organisationsite'
EXEC master..xp_sendmail @.recipients = 'Bertrandk@.Holala.com',
@.subject = 'Test mail',
@.query =@.foo,@.no_header= 'TRUE',
@.message='some attachement',
@.attach_results = 'TRUE'
This does not work, the spaces are still in the file
Is there something I can do to correct this?
Thanks in anticipation for your help.

Hi,

what about converting your whole message to VARCHAR ? Will that work in your situation, or do you need additional stored information in UNICODE from your columns ?

set @.foo='select top 4 CONVERT(VARCHAR(4000),Replace(priMarykey,Nchar(0020),'')) (...)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||I tried that but it did not help. I would like to know if everyone just uses notepad to open sql attachements or if they do use other text editors, do they also get the same problem that I am getting?|||

No, never has that problem, although dealing with UNICODE data. Could you please post you table definition in here ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de