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

• Microsoft SQL Server, version 6.5

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

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

CAUSE

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

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.