TOS 6.1.1 - Losing Carriage Return/Line Feed

Six Stars

TOS 6.1.1 - Losing Carriage Return/Line Feed

I have a String field.  In one part of my Job I add 2 Carriage Return/Line Feeds to that string using a tJavaRow:

 

myString += "\r\n\r\n";

 

I put a tLog all the way at the end of the Job, and the CRLFs are displaying.  But when I replace the tLog with a tMssqlOutput, the resulting value in the database has its CRLFs replaced with spaces (1 space per CR, and 1 space per LF).  I could swear I've injected CRLFs into Strings in Talend in the past and they've made it all the way into the target database field.  I'm baffled.

 

I've also tried using a tMap instead of the tJavaRow.  Same result.

 

Any thoughts? TIA!


Accepted Solutions
Sixteen Stars

Re: TOS 6.1.1 - Losing Carriage Return/Line Feed

SQL Server will not display the values in your query tool with carriage returns. But if you read the values back into Talend and use a tLogRow you will see that the carriage returns are there. If you copy the cell field from your query tool and paste it into a text file, you will see the value is correct. What you see in your query tool is not necessarily exactly what is there. 


All Replies
Sixteen Stars

Re: TOS 6.1.1 - Losing Carriage Return/Line Feed

SQL Server will not display the values in your query tool with carriage returns. But if you read the values back into Talend and use a tLogRow you will see that the carriage returns are there. If you copy the cell field from your query tool and paste it into a text file, you will see the value is correct. What you see in your query tool is not necessarily exactly what is there. 

Six Stars

Re: TOS 6.1.1 - Losing Carriage Return/Line Feed

Smiley Frustrated

 

OK. My bad.  Well... partially.  Here's what was throwing me off in case someone with the same issue finds this thread.

 

I recently upgraded my SQL Server Management Studio from v12 (shipped with SQL2014) to the latest v14.

Sidebar:   v14 is also known as v17.4???  I have no idea what Microsoft is doing over there

 

ssms.png

 

Anyway, when there are Carriage Return / Line Feeds in a MSSQL field here's what you get from SSMS v12:

 

3-26-2018 1-03-39 PM.png

 

Perfect!  That is exactly what you would expect!

But let's see that in the latest SSMS:

 

3-26-2018 1-18-34 PM.png

 

Nonetheless, the CRLFs are there and the string is formatted properly in the client application.

 

Very frustrating.  But there you have it.

 

EDIT:  Since retaining CRLF was default in previous versions (and there was no option that I know of to turn it off) you'd think this "improvement" would have the option enabled by default.  But no... more confusion caused by goofiness from Microsoft.

 3-26-2018 1-39-40 PM.png