BCP Utility Unable to Export Data in Linux Using JAVA:

Question

I tried to execute the following piece of code to export data(myFileName.csv) :

bcp "select * from DataBase.schema.TABLE_NAME" queryout tableData.csv -c -t, , -S [server] -U [user] -P '[password(with special characters)]' > LogFile.txt 

the above code is working fine in terminal.

In contrast, I tried the same piece of code using java.

File dir = new File("Mydirectory");
    Path dataPath = Paths.get("tableData.csv");
    List<String> val = new ArrayList();
    val.add("bcp");
    val.add("\"select * from " + [Database] + ".[Schema]." + table_name + "\"");
    val.add("queryout");
    val.add(dataPath.toString());
    val.add("-c");
    val.add("-t");
    val.add(",");
    val.add("-S");
    val.add([server]);// ex: if Server is 10.0.0.1  then val.add("10.0.0.1");
    val.add("-U");
    val.add([user]); // ex: if User_name is TestA then val.add("TestA");
    val.add("-P");
    val.add([password(with special characters)]); // ex: if Password is !@#MyPassword*& then val.add("!@#MyPassword*&");
    ProcessBuilder builder = new ProcessBuilder(val);
    File logFile = new File("LogFile.txt");
    System.out.println("BCP command :" + builder.command());
    builder.redirectlogFile(logFile);
    builder.directory(dir);
    Process exec = builder.start();
    System.out.println("BCP process completed : with errors :" + exec.waitFor());
    System.out.println("BCP logFile :" + org.​apache.​commons.​io.FileUtils.readFileToString(logFile));

I got the following error:

BCP command :[bcp, "select * from DataBase.schema.TABLE_NAME", queryout,tableData.csv, -c, -t, ,, -S, 10.0.0.1, -U, TestA, -P, !@#MyPassword*&]

BCP process completed : with errors :1

BCP logFile : Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations

SQLState = 37000, NativeError = 102 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'select * from DataBase.schema.TABLE_NAME'.

By looking the error I checked the collations of Server, Database and Table all seems similar SQL_Latin1_General_CP1_CI_AS

System Specifications :

Linux :
    uname -mrs
        Linux 3.10.0-327.10.1.el7.x86_64 x86_64
    uname -a
        Linux [domain] 3.10.0-327.10.1.el7.x86_64 #1 SMP Tue Feb 16 17:03:50 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
    cat /etc/redhat-release
        CentOS Linux release 7.2.1511 (Core)

ODBC Driver for linux:
    isql --version
        unixODBC 2.3.0

    odbcinst -q -d -n "SQL Server Native Client 11.0"
        [SQL Server Native Client 11.0]
        Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
        Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0

Any anyone help me What i am doing wrong over here.

Answer 1

I want to share with you some suggestions. You may have try. Hope it will solve your issue.

Suggestion-1:

Sometimes this problem occurs if we change collation of column or make any changes of table/view own properties. One thing you could do is to check the collation setting of columns b\w source & destination table and then use COLLATE clause in your source script to change the collation to match that of destination. This problem can be solved by recreating the table/view. So I will suggest you to again create the "TABLE_NAME" from your "DataBase.schema"

Suggestion-2:

Recommended solution:

Sometimes queryout is not worked and gives error. On that case, if we use print then this problem is solved. You can go through this section: SQL query using bcp not working with queryout as a variable

Suggestion-3:

Tough task but useful:

You may try to remote connect to the server and run the bcp without the -S option from your code. Remove the below portion and make a try:

val.add("-S");
val.add([server]);// ex: if Server is 10.0.0.1  then val.add("10.0.0.1");

If the server collation is different from the database or column collation, try to create the format file and explicitly specify it in the bcp.

Suggestion-4:

Sometimes null and bulk insert may also cause this problem. you can go through: Bulk Insert Collation Error

Suggestion-5:

This problem was occurred in SQL Server 2005 Native Client. But microsoft will not fix this in previous version. They have fixed it in SQL Server 2008 Native Client. For more details you can go through: BCP Error - Unable to resolve column level collations - by krishnc