I believe that this is an encoding issue between the shell and bcp / SQL Server. SQL Server expects UTF-16 Little Endian, but Linux isn't using that. The default for my Linux VM is UTF-8 via
<TL;DR> Use the "queryout" bcp command and specify
"SELECT * FROM ..." instead of using the "out" command and simply supplying a table name.
What follows is my testing...
I got the list of available locales / encodings using:
$ locale -a
I tried several of those options by setting:
$ export LC_CTYPE=C.UTF-8
and then trying again using:
$ export LC_ALL=C.UTF-8
Nothing seemed to make a difference. And each time I tried with various combinations of square brackets without
-q and then with
-q, and then no square brackets both with and without
I even tried injecting the bytes that would equate to the UTF-16 LE character of
$'\xe4\x00' and even
$'\xe4'$'\x00', but no improvements.
what did work was changing the bcp command from
out to instead be
queryout, and then changing the table name to be part of a
SELECT statement (I removed the
-r ~ switch only here to make the command line not scroll horizontally, but it was in my testing). I created the table in
[tempdb] and ran the following:
bcp "SELECT * FROM tempdb.dbo.[Täble_Name]" queryout tab -c -k -S127.0.0.1 -Usa -Ppass
No problems there. But interestingly enough, I changed the accented
ä to a non-accented
bcp "SELECT * FROM tempdb.dbo.[Table_Name]" queryout tab -c -k -S127.0.0.1 -Usa -Ppass
and received the following error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
That is an error from bcp and must be referring to the meta-data of
tempdb since the only column in my test table uses the
Now, my instance-level Collation is accent-sensitive, so I wasn't really expecting the non-accented
a to work (though I did expect an "invalid object" error). So, in order to test accent-insensitivity, I created a new Database with a Collation of
Latin1_General_100_CI_AI_KS_WS_CS, create that same table in the new DB, and added a few rows. I then ran the following two tests:
bcp "SELECT * FROM ImportTest.dbo.[Täble_Name]" queryout tab -c -k -S127.0.0.1 -Usa -Ppass
bcp "SELECT * FROM ImportTest.dbo.[Table_Name]" queryout tab -c -k -S127.0.0.1 -Usa -Ppass
and both worked!
Going back to the initial bcp command of just specifying the table name instead of a query, I was able to get
ImportTest.dbo.Table_Name to work. However, I was still not able to get any combination of
ImportTest.dbo.[Täble_Name] to work; all variations got the same errors as before.