11

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

7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.