3

Using BCP from mssql-tools on CentOS7 and trying to copy some TSV data into a local MSSQL DB, BCP fails to do the copy and throws error "BCP copy in failed". The command being run is:

TO_SERVER_ODBCDSN="-D -S MyMSSQLServer"
RECOMMEDED_IMPORT_MODE='-c' # makes a big difference, see https://stackoverflow.com/a/16310219/8236733
/opt/mssql-tools/bin/bcp "$TABLE" in "$filename" \
        $TO_SERVER_ODBCDSN \
        -U $USER -P $PASSWORD \
        -d $DB \
        $RECOMMEDED_IMPORT_MODE \
        -t "\t" \
        -e ${filename}.bcperror.log

Yet the error logs created by the command are empty. What does this mean / imply? Anyone have any further debugging tips for resolving the "copy in failed" error?

lampShadesDrifter
  • 3,925
  • 8
  • 40
  • 102
  • Looking here: https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017 I dont see the "-D" option as a valid option. What is this doing in your command? Also, can you share the actual output/return from BCP when you get your error? – jamie Sep 20 '19 at 15:42
  • 1
    @jamie "-D option: Causes the value passed to the bcp -S option to be interpreted as a data source name (DSN)." I use this for using a /etc/odbc.ini file rather than a IP string literal. See https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-bcp?view=sql-server-2017 – lampShadesDrifter Sep 20 '19 at 19:58
  • Ah, gotcha. I have not used BCP in an env other than Microsoft. Good to know I had always assumed the bcp executable would be the same. – jamie Sep 20 '19 at 20:09

1 Answers1

2

The errorlog created when you use the -e option is meant to capture errors regarding the data itself. So, the errorlog will contain errors when there is an overflow of data (too many bytes in a field destined for a column with too few).

Execution errors, or errors with the BCP application itself are not captured in the error file created by the -e option.

In an automated environment, if you want to capture or log such errors you will need to redirect the output of the BCP command to a file for viewing later or even loading into a log table in a SQL table.

jamie
  • 745
  • 4
  • 11
  • 1
    Worth noting that BCP writes its execution errors to STDOUT, not STDERR as one might expect. Wouldn't it be great if BCP execution errors AND -e data errors were logged to STDERR? – Trutane May 21 '20 at 23:47