Line Feed and Caradge Return in SQL Statement

Are you sure SQL has pa problem with CR/LF? normal SQL is no problem.
anyway, to remove CR/LF you can use ;

replace( replace ( @Mystring, CHAR(13),''), CHAR(10), '')

Are you using xp_readmail to read the message?  @message is text datatype, not varchar, so make sure you are not just truncating the message at the length of your declared @message variable (and it just happens to end @ CRLF.)

Hi,

Well BillAn1, I am trying to use the replace function, I tried it last night, but somehow, it doesnt seem to work. Still trying. :). And yes Kselvia I am using xp_readmail, but how can I check/change the datatype of text that is in the message body. Can we apply this replace function to replace what we want in the text for xp_readmail @message variable. Kselvia, by truncating the message means deleberately applying CRLF through code? if so, no I am not doing that.

Regards.

Moazzam

You will need to convert the @message variable to a varchar before you use the REPLACE function. THe risk of truncating is that varchar is maximum length of 8000 characters. If your text is longer than that you will loose the end of it.

declare @MyString varchar(8000)
set @MyString = @message
MyString = replace(replace(@MYString,char(10),''),char(13),'')

however, can youcalrify what the error is you are getting? why are you having problems wiht CRLF?

Hi BillAn1,

Well I am getting this query in mail and this is the error at the bottom of the query that I get.

insert into Quandary.dbo.cases (Company,[UPL Call No],[SR
No],Technology,Hardware,Software,System,[Problem Description],[Case
Status],Caller,[Caller Email],[Customer site],[Open
Date],Summary,Engineer,City,Component,Product,[Contract
Type],Priority,[Entered By],[Entered Time]) values ('ENI Pakistan
Ltd','ENI-410008',' ','Networking','-- N/A --','-- N/A --','fgfgsfd fsdg
gd f','sgd fg gsdfgfsdgfdg','Open','Asif Mansoor','
','Forum','09/01/2004 4:45:09 PM','fgdsfg fgf sggsdgsdfgdfgfg
fgsddf','Muhammad Akif','Karachi','12/24GB DAT Drive','Brio BA
410','Maintenance','Normal','Muhammad Akif','09/01/2004 4:45:39 PM');

ODBC error 207 (42S22) Invalid column name 'SR

No'.

What SQL xp_readmail reads after the "SR" is CRLF, I have tried several ways to avoid these characters but, was'nt able to resolve this issue, as these lines of xp_readmail doesnt allow any thing like variable assignment and applying functions while this procedure is executed.

 exec @status = master.dbo.xp_readmail
            @msg_id=@msg_id,
            @originator=@originator output,
            @cc_list=@cc_list output,
            @subject=@msgsubject output,
            @message=@query output,
            @peek='true',

these are the lines that I got from sp_processmail, I think the last variable assignment and function application solution you mentioned does'nt fit here.

I have asked my programers to apply some changes at there code to getrid of these CRLF special characters. Lets see what they comeup with. If this replace function would have been worked here properly, it would have been a great stored procedure, no worries at all.

Regards.

Moazzam

Hi BillAn1,

Gr8 help, it worked fine. I saved alot of time. Once again very very thanks.

Regards.

Moazzam