Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2004-10-24 : 23:15:49
|
| i've created a trigger on a table which has all the information i need to send mails.inserts to this table fires the trigger, gets the row inserted, pass the data as parameters to the exe file, which then sends the mail.i'm using master..xp_cmdshell to call the exe file:exec master..xpcmdshell @sqlthe @sql is varchar(4000) and will retrieve the data from the table e.g. 'To:mymail@mail.com|Subject:mysubject|Body:message here'problem is, whenever the body/message has linefeed/carriage/tab return in it, it cuts the message to that point.i've thought of creating a file and attaching it instead but come to think of it, the entire length does not exceed 4,000 characters.is there a workaround this one?--------------------keeping it simple... |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-25 : 01:23:47
|
| So its the CR+LF that are mucking it up?Does the message display "pretty" [in, say, Outlook] if it contains very simple HTML? For example, just wrap the messsage in <HTML><BODY> and REPLACE all the CR/LF with <BR> ...Just a thought, probably useless!Kristen |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-25 : 02:02:39
|
ow, the sqlmail term was probably confusing.  guilty...i'm using lotus notes to send the mail ,here's the procedure:1. insert into tblSendMail entries2. trigger is fired - the exe file is pretty straightforward, if i call it in command prompt, the entire message appears in body but if in trigger, truncates it.<code> create trigger trgSendMail on tblSendMailfor insertas if update(idx) and (select status from inserted)=0 begin declare @sqlstring varchar(4000),@err int set @sqlstring = (select coalesce('"To:' + i.MailTo,'') + coalesce('|BCC:' + i.MailBCC,'') + coalesce('|CC:' + i.MailCC,'') + '|Subject:' + i.MailSubject + '|Body:' + i.MailBody + '"' from inserted i) set @sqlstring= 'c:\progra~1\sendmail\sendmail.exe ' + @sqlstring exec @err=master..xp_cmdshell @sqlstring if @err=0 update tblsendmail set status='1' where idx=(select idx from inserted) else insert into tblsendmail(transactiondate,MailTo,MailSubject,MailBody,Status) values(getdate(),'mymail@mail.com','Mail not sending', 'Mail not sending from ServerA, file c:\progra~1\sendmail\sendmail.exe.Check MailErrMess.txt',1) end</code>my guess is that it has something to do with the 4000 limit of varchar. i tried exploring text but can't concat it with other details.--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-25 : 08:06:34
|
| "exec @err=master..xp_cmdshell @sqlstring"That's where you CR+LF is breaking it then ... the command-line-command will terminate at the first CR - and the stuff next-up after the CR had better not be DELETE *.* - coz it will be the next command in line!You may be able to use "\n" instead of CR+LF in sendmailKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-25 : 23:48:42
|
quote:
Originally posted by Kristen
"exec @err=master..xp_cmdshell @sqlstring"That's where you CR+LF is breaking it then ... the command-line-command will terminate at the first CR - and the stuff next-up after the CR had better not be DELETE *.* - coz it will be the next command in line!You may be able to use "\n" instead of CR+LF in sendmailKristen
thanks kristen,i honestly didn't know that. i tried this in the body 'this is a test \n next line'but it didn't work, it treated \n as characters.--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-26 : 01:04:52
|
| Will notes take "rudimentary HTML" in its message body? That would allow the use of "<BR>", otherwise you'll have to talk to SendMail [or equivalent] direct so you can transfer a block of text, including line breaks.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-26 : 02:12:05
|
quote:
Originally posted by Kristen
Will notes take "rudimentary HTML" in its message body? That would allow the use of "<BR>", otherwise you'll have to talk to SendMail [or equivalent] direct so you can transfer a block of text, including line breaks.Kristen
i guess, the fastest way is to directly do it in sendmail if command line doesn't work for CR.--------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-03 : 06:38:28
|
| for those interested, i retained the ddl for the table and did not alter the trigger, instead, i modified the exe to replace <BR> for crlf when creating the body of the mail, which i placed for every crlf that i need.--------------------keeping it simple... |
|
|
|
|
|
|
|
|
sqlmail mail body gets truncated...