BCP OUT using Error file makes sense?

Question

BCP OUT using Error file makes sense?

Hans van Dam

SSC-Addicted

Points: 486
More actions
March 21, 2017 at 2:49 am

#100089

Hi all,
I use BCP very often, normally with the -e err_file option.
When specified, the err_file is created on running a BCP OUT but I cannot recall to have ever seen any content in those cases.
Documentation states it is used "to store any rows that the bcp utility cannot transfer from the file to the database", thus suggesting it's only useful with the IN and not with the OUT or QUERYOUT options.
Am I correct to say that an err_file only makes sense with BCP IN?
Any reaction is wellcome, thanks.

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

I've never used the error file option with BCP OUT. That's usually because everything has been tested to the extreme and "all" errors and bad validations have already been caught because I also try to import the file before I ship it.

That, not withstanding, there's the subject of change. If someone changes the source table or does some other thing that may cause an error and they've not been so careful, then maybe the error file option of BCP OUT would make sense. This would be especially true if someone realized the value of using a BCP Format File even for simple exports. The format file would be the ultimate "final check" at least for what the result file should look like.

An example of this is what I went through with a Developer just yesterday. He was getting a file that had such a header, followed by a blank line, followed by the data lines. The blank line would normally have provided him no obstacle except that both the header line and the blank line ended with just a line-feed character whereas the rest of the lines ended with carriage-return/line-feed characters. As a bit of a side bar, it was really hard to find because the hex reader software that he was using for troubleshooting would automatically change the single line feed characters to carriage-return/line-feed combos. Drove him a bit nuts until I showed him.

Getting back to the subject, if the folks that created the file had used a BCP Format File to create both the header and the body of the file, there'd have been no such problem because either the data would have been EOL terminated correctly or an error would have occurred... especially for the blank line. Then, using the error file option of BCP OUT may have alerted them to the problem.

Of course, the real key is that they didn't actually test the file they had generated. If they had tried to import the file themselves before shipping it to us, they'd have realized they had done something wrong and would have (hopefully) fixed it.

--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)

Hans van Dam SSC-Addicted Points: 486 More actions · Answer 2

Jeff Moden - Tuesday, March 21, 2017 8:07 AM
I've never used the error file option with BCP OUT. That's usually because everything has been tested to the extreme and "all" errors and bad validations have already been caught because I also try to import the file before I ship it.
That, not withstanding, there's the subject of change. If someone changes the source table or does some other thing that may cause an error and they've not been so careful, then maybe the error file option of BCP OUT would make sense. This would be especially true if someone realized the value of using a BCP Format File even for simple exports. The format file would be the ultimate "final check" at least for what the result file should look like.
An example of this is what I went through with a Developer just yesterday. He was getting a file that had such a header, followed by a blank line, followed by the data lines. The blank line would normally have provided him no obstacle except that both the header line and the blank line ended with just a line-feed character whereas the rest of the lines ended with carriage-return/line-feed characters. As a bit of a side bar, it was really hard to find because the hex reader software that he was using for troubleshooting would automatically change the single line feed characters to carriage-return/line-feed combos. Drove him a bit nuts until I showed him.
Getting back to the subject, if the folks that created the file had used a BCP Format File to create both the header and the body of the file, there'd have been no such problem because either the data would have been EOL terminated correctly or an error would have occurred... especially for the blank line. Then, using the error file option of BCP OUT may have alerted them to the problem.
Of course, the real key is that they didn't actually test the file they had generated. If they had tried to import the file themselves before shipping it to us, they'd have realized they had done something wrong and would have (hopefully) fixed it.

Hi Jeff, thanks, but I guess I wasnt clear. What I actually meant is this, considering:
1: the documentation states the err_file is used for "rows that the bcp utility cannot transfer from the file to the database";
2: BCP OUT does not transfer from the file to the database but the other way around.

Would it technically be possible to get content in the err_file on BCP OUT?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Hans van Dam - Tuesday, March 21, 2017 8:33 AM
Hi Jeff, thanks, but I guess I wasnt clear. What I actually meant is this, considering:
1: the documentation states the err_file is used for "rows that the bcp utility cannot transfer from the file to the database";
2: BCP OUT does not transfer from the file to the database but the other way around.
Would it technically be possible to get content in the err_file on BCP OUT?

Like I said, I've never tried. You're correct that the documentation you've cited seems to indicate that an err_file can't be used with BCP OUT but the documentation also doesn't recognize \" as a valid delimiter even though it works as expected. The documentation may only be covering expected use because the authors may not have considered otherwise. I'd say that the only way to know for sure is to try it.

--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)

Hans van Dam SSC-Addicted Points: 486 More actions · Answer 4

Jeff Moden - Tuesday, March 21, 2017 10:12 PM
Hans van Dam - Tuesday, March 21, 2017 8:33 AM
Hi Jeff, thanks, but I guess I wasnt clear. What I actually meant is this, considering:
1: the documentation states the err_file is used for "rows that the bcp utility cannot transfer from the file to the database";
2: BCP OUT does not transfer from the file to the database but the other way around.
Would it technically be possible to get content in the err_file on BCP OUT?
Like I said, I've never tried. You're correct that the documentation you've cited seems to indicate that an err_file can't be used with BCP OUT but the documentation also doesn't recognize \" as a valid delimiter even though it works as expected. The documentation may only be covering expected use because the authors may not have considered otherwise. I'd say that the only way to know for sure is to try it.

I almost always did use it so it's valid. Can't think of any scenario there will be contents though. I think I'll not be using it anymore unless it's BCP IN.