Solved

Line Feed and Caradge Return in SQL Statement

Posted on 2004-09-01
7
377 Views
Last Modified: 2008-02-01
Hi,

I am using 2 SQL Servers, 1 is a Production Server and 2 Live database server. I use to insert records in Server 1 and while inserting through VB code i generate an email that gets the data to Server 2 and inserts values there too. Now the problem is when Outlook at Server 2 gets the mail and try processing the mail from SQL Mail feature it gives error. When I use that statement and try to debug and check the hex value at DOS prompt the line where the error was given, contains line feed and caradge return, and that makes SQL Server 2 not to recognize the Query properly. Is there any feature/solution to overcome this problem.

Regards.

Moazzam
0
Comment
Question by:moazzam1574
  • 3
  • 3
7 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11952949
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), '')
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11959912
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.)
0
 

Author Comment

by:moazzam1574
ID: 11960395
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
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 17

Expert Comment

by:BillAn1
ID: 11961325
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?
0
 

Author Comment

by:moazzam1574
ID: 11962227
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
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 100 total points
ID: 11962381
Yes, if CRLF are actually in the middle of calumn names etc, it will cause a problem.

From the look of the data you have here, I think what you need is to replace CRLF with a space  
e.g. is the correct name of the column [SR No] ?
in your email it is [SR{CR}{LF}No]

try this (replace the print with EXEC when you are happy) - it replaces CR with ' ' and LF with ''

declare @q2 varchar(2000)
set @q2 = replace(replace(@query,char(13), ' '), char(10), '')
print @q2
---exec (@q2)

 this give the following result, which looks OK to me:

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')
0
 

Author Comment

by:moazzam1574
ID: 11970649
Hi BillAn1,

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

Regards.

Moazzam
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
create an aggregate function 9 36
Query to capture 5 and 9 digit zip code? 4 22
SQL - Update field defined as Text 6 17
replace \ by - in select 4 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question