You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q164291
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
Microsoft SQL Server identifies a Transact-SQL command containing thebackslash (\) 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 fromSQL 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 secondinsert, 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, replacingthe \<cr><lf> with \\<cr><lf><cr><lf> before sending the query to the SQLServer.
Properties

Article ID: 164291 - Last Review: 12/04/2015 16:26:16 - Revision: 3.3

Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbprb KB164291
Feedback