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
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.
I want to share with you some suggestions. You may have try. Hope it will solve your issue.
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"
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
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.
Sometimes null
and bulk insert
may also cause this problem. you can go through:
Bulk Insert Collation Error
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With