BCP adding carriage return/new line every 2034 characters

Question

BCP adding carriage return/new line every 2034 characters

timbroadaway

Old Hand

Points: 329
More actions
December 30, 2021 at 8:38 pm
Go to Answer
#3968536
I have an SQL query that creates a JSON output. The query works properly and gives me the output I expect when I manually run it via SSMS. When I run it in a BCP command from an SP, the BCP utility is adding carriage returns and new lines every 2034 characters. I can open the output in a hex editor and I can see the 0d 0a every 2034 characters. I've tried using the -r switch with all sorts of options and I can't get it to work. Does anyone know why BCP is adding these characters into my file and how I can fix it?
- This topic was modified 2 years, 10 months ago by timbroadaway.
timbroadaway

Old Hand

Points: 329
More actions
December 31, 2021 at 12:20 am
Answer
#3968576

I found an answer. This forum post https://github.com/MicrosoftDocs/sql-docs/issues/1501 was talking about the 2034 new line character problem. Which then has a link to this forum post https://stackoverflow.com/questions/5655332/using-sql-server-for-xml-convert-result-datatype-to-text-varchar-string-whate/5658758#5658758
In that post it says to wrap your SQL query in another SELECT statement that casts the original query as a varchar(max). So that is what I did, and it fixed my problem. Now BCP extracts the data like it is supposed to without any carriage returns or new lines.
Hope this helps someone!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

your expectation may not be what you can generate from sql using BCP - not knowing what options you are using I would advise using

"-c" which will add a \r\n after each row. (or -w if your data is unicode)

timbroadaway Old Hand Points: 329 More actions · Answer 2

Thanks for the reply. I do use -c. However, that is what is interesting. When I run the query manually in SSMS, the output is all on one single line. There are no line breaks in it. But when I run the query via BCP, the line breaks are entered. That's what I don't understand. Why is BCP adding the line breaks when the output directly from the query is just one big single line?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

ssms is not BCP - and what you get on SSMS is in many cases totally out of sync with what you get with other tools.

on this case SSMs applies ZERO formatting to the string and it puts it on a single line - but it may truncate it if its too big. bcp won't do that - but will do other things.

the option -c will always add the \r\n at the end of a record - regardless of its size - maybe on your case that means after 2033 characters - without an example we won't know if that is what it is.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

timbroadaway wrote:

Thanks for the reply. I do use -c. However, that is what is interesting. When I run the query manually in SSMS, the output is all on one single line. There are no line breaks in it. But when I run the query via BCP, the line breaks are entered. That's what I don't understand. Why is BCP adding the line breaks when the output directly from the query is just one big single line?

If you're in the Grid mode for results, you won't see any line breaks. Try the Text mode and see what you get. I'm betting you'll get the line break because BCP doesn't add them as you claim. Rather, they're already there but you can't see them in the grid mode.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)