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.

Answer this question

Attachement generated by xp_sendmail

Spider-X
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
---
Rujith Anand
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
Srichris
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
---
Attachement generated by xp_sendmail

Answer this question