Sql-server – bcp command Incorrect syntax near ‘�’. Character is actually: “ä”

bcplinuxsql serversql-server-2017Ubuntu

I have mssql-server and mssql-tools installed on Ubuntu (Linux). When I try to export data with the bcp command using the following command-line:

bcp DBname.dbo.Täble_Name out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~

I get this error:

SQLState = 37000, NativeError = 102
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '�'.

The � is ä.

If I surround the Täble_Name with square-brackets: 

 bcp DBname.dbo.[Täble_Name] out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~

I get this error on the object name:

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'DBname.dbo.Täble_Name'.

I went further and added single-quotes '' along with the -q option (which enables Quoted Identifiers):

bcp 'DBname.dbo.[Täble_Name]' out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~ -q

The error becomes :

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'DBname.dbo.T�ble_Name'.

NB: the command works perfectly with table names without this special character ä .

Best Answer

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 en_GB.UTF-8.

<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

returned:

C
C.UTF-8
en_AG
en_AG.utf8
en_AU.utf8
en_BW.utf8
en_CA.utf8
en_DK.utf8
en_GB.utf8
en_HK.utf8
en_IE.utf8
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ.utf8
en_PH.utf8
en_SG.utf8
en_US.utf8
en_ZA.utf8
en_ZM
en_ZM.utf8
en_ZW.utf8
POSIX

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 -q.

I even tried injecting the bytes that would equate to the UTF-16 LE character of ä via $'\xe4\x00' and even $'\xe4'$'\x00', but no improvements.

HOWEVER,

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 a:

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 INT datatype.

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] and 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.

Related Question