TOS 6.1.1 - Losing Carriage Return/Line Feed

[PaulyWally]

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!

[Anonymous · Accepted solution]

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. 

[PaulyWally]

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

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

Perfect!  That is exactly what you would expect!

But let's see that in the latest SSMS:

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.