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

 

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.