Sybase 12.5: how to bcp in/out a table with a 'text' field

Question

I have a table of this structure

create table test {
  id_data int identity,
  data text
  // id_data is the primary key
}

I used the following commands:

bcp DB..test out prod.bcp.out -U me-P pwd -SPROD -I ~/bin/interfaces -c -T40960
bcp DB..test in prod.bcp.out -U me -P pwd -SUAT -I ~/bin/interfaces -E -c -T40960

And the bcp in emits the following message:

CSLIB Message:  - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.
bcp copy in failed

Answer 1

I've had similar issues when bulk copying text field data. It's possible that you have characters in your data which are being misinterpreted by BCP as field or line delimiters.

Try explicitly setting field and line delimiters.

bcp DB..test out prod.bcp.out -Ume -Ppwd -SPROD -I ~/bin/interfaces    -t#@# -r\\n -c -T40960
bcp DB..test in  prod.bcp.out -Ume -Ppwd -SUAT  -I ~/bin/interfaces -E -t#@# -r\\n -c -T40960

I used #@# here because it was recommended here since it's more unique than the standard options (bars, commas, tabs, etc.).