Auto e-mails

Hello,

I have a SQL server though a hosting company and I am trying to send autoemails using xp_sendmail. The permissions were set and I used the following command to test it.

EXEC master.dbo.xp_sendmail
@.recipients='tracey@.yahoo.com',@.subject='test',@.me ssage='testing
sql stored procedure'

It gave me a message saying "Mail sent" but there none in my e-mail box.

How do I set yp the SQL Mail server, right? Please help. I don't know what is happening.

Thanks,
TraceyHave you checked whether the Mail Service has been turned on and the machine has MS Outlook installed(And it has to work)?|||And an Outlook need to have mail configured using the same account the SQL Server Agent runs as.|||Insead of all that, you could use CDO to send mail, which eliminates the need to have Outlook installed.

Check out this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_cdo_for_nts_library.asp

and this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp

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