Skip to main content
Announcements
A fresh, new look for the Data Integration & Quality forums and navigation! Read more about what's changed.
cancel
Showing results for 
Search instead for 
Did you mean: 
PaulyWally
Contributor III

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!

Labels (3)
1 Solution

Accepted Solutions
Anonymous
Not applicable

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. 

View solution in original post

2 Replies
Anonymous
Not applicable

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
Contributor III
Author

0683p000009M9p6.png

 

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

 

0683p000009Lss4.png

 

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

 

0683p000009LtGx.png

 

Perfect!  That is exactly what you would expect!

But let's see that in the latest SSMS:

 

0683p000009LtH2.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.

 0683p000009LtH7.png