bcp file into SQL Server within start with "0x" binary field on linux

Question

Hi,

I have a data file which has a binary field. And this field value is start with "0x". I want to know is there a way to put the data into sql server database via bcp? I check the normal method, it returned error "String data, right truncation".

BTW, my db server is on linux.

mssql@3a9d83021b4f:/opt/mssql-tools/bin$ ./bcp test_msdb..price in test_out_binary.bcp -c -t'|' -S localhost -Usa -P ****

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation

BCP copy in failed

Answer 1

Yes, it is possible, but you will need a format file where you define a fixed-length field for the 0x with 0 as the target column to specify that it is not to be imported.

Here is a simple example. First a table*:

   CREATE TABLE sjaskig(a varbinary(50))  

Then a data file:

   0x47114711ABCDABCD  

Next, the format file:

   9.0  
   2  
   1 SQLCHAR 0 2  ""     0 "" ""  
   2 SQLCHAR 0 2  "\r\n" 1  a ""  

I have \r\n since I'm on Windows. Change to \n for Linux.

Here is the BCP command:

   bcp tempdb.dbo.sjaskig in C:\temp\slask.bcp -f slask.fmt -S Server -T  

If you want to learn about format files, see this article on my web site:
https://www.sommarskog.se/bulkload.html.