PRB: Proper Handling of the Escape Sequence \<cr><lf>

Article translations Article translations
Article ID: 164291 - View products that this article applies to.
This article was previously published under Q164291
Expand all | Collapse all

SYMPTOMS

Microsoft SQL Server identifies a Transact-SQL command containing the backslash (\) followed by the carriage return (0x0d) and line feed (0x0a) as an escape sequence.

The following example demonstrates the behavior:
   use pubs
   go
   drop table tblTest
   go
   create table tblTest
   (
      strData varchar(30)
   )
   go
   insert into tblTest values ("c:\ 
   X")
   go
   insert into tblTest values ("c:\\ 
   X")
   go
   select * from tblTest
   go
				

   strData
   ------------------------------
   c:X
   c:\ 
   X
				

The first insert contains the \<cr><lf> sequence, and when returned from SQL Server, is c:X. The escape sequence is scanned out by the SQL Server.

However, replacing the \<cr><lf> with \\<cr><lf><cr><lf>, as in the second insert, yields the expected results.

CAUSE

SQL Server sees the sequences \<cr><lf> (\ + 0d + 0a) and \<lf> (\ + 0a) as escape sequences.

WORKAROUND

To work around this problem, correct the Transact-SQL string, replacing the \<cr><lf> with \\<cr><lf><cr><lf> before sending the query to the SQL Server.

Properties

Article ID: 164291 - Last Review: March 1, 2005 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbprb KB164291

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com