bcp fails with table with text fields

[joe]

Hi I was able to bcp out all my tables except for one. I ran the dbcc
command to repair text fields but I got the same problem. I also tried
select into another table. I got many lines of the "Data truncated "
error and then I got this

blk_rowxfer(): blk layer: internal BLK-Library error: Data truncated
while doing local character set conversion. col = 10
*** glibc detected *** bcp: corrupted double-linked list: 0x087f64a0
***
======= Backtrace: =========
/lib/libc.so.6[0xa94f82]
/lib/libc.so.6(realloc+0x19d)[0xa96ebd]
bcp(comn_realloc+0x1e)[0x813093a]
bcp[0x80839a9]
bcp(blk__rowxfer_in+0xc6)[0x8081686]
bcp(blk__async_continue+0x77)[0x8085b43]
bcp(blk_rowxfer_mult+0x8d)[0x8081739]
bcp[0x80706c5]
bcp(executeBulkCopy+0x3d)[0x8070001]
bcp[0x806db2f]
bcp(runThread+0x5b)[0x806d7df]
bcp(runSessions+0x30)[0x806d868]
bcp(ctbcp_main+0x439)[0x8066b3d]
bcp(main+0x9e)[0x8067066]
/lib/libc.so.6(__libc_start_main+0xdc)[0xa3fe8c]
bcp(dlopen+0x41)[0x8066659]
======= Memory map: ========
0024b000-00256000 r-xp 00000000 08:01 21201109 /lib/
libgcc_s-4.1.2-20080825.so.1
00256000-00257000 rwxp 0000a000 08:01 21201109 /lib/
libgcc_s-4.1.2-20080825.so.1
004c9000-004ca000 r-xp 004c9000 00:00 0 [vdso]
00531000-0053a000 r-xp 00000000 08:01 21200936 /lib/
libnss_files-2.5.so
0053a000-0053b000 r-xp 00008000 08:01 21200936 /lib/
libnss_files-2.5.so
0053b000-0053c000 rwxp 00009000 08:01 21200936 /lib/
libnss_files-2.5.so
00967000-0097a000 r-xp 00000000 08:01 21202160 /lib/libnsl-2.5.so
0097a000-0097b000 r-xp 00012000 08:01 21202160 /lib/libnsl-2.5.so
0097b000-0097c000 rwxp 00013000 08:01 21202160 /lib/libnsl-2.5.so
0097c000-0097e000 rwxp 0097c000 00:00 0
00a07000-00a21000 r-xp 00000000 08:01 21202146 /lib/ld-2.5.so
00a21000-00a22000 r-xp 00019000 08:01 21202146 /lib/ld-2.5.so
00a22000-00a23000 rwxp 0001a000 08:01 21202146 /lib/ld-2.5.so
00a2a000-00b68000 r-xp 00000000 08:01 21202147 /lib/libc-2.5.so
00b68000-00b6a000 r-xp 0013e000 08:01 21202147 /lib/libc-2.5.so
00b6a000-00b6b000 rwxp 00140000 08:01 21202147 /lib/libc-2.5.so
00b6b000-00b6e000 rwxp 00b6b000 00:00 0
00b70000-00b95000 r-xp 00000000 08:01 21202154 /lib/libm-2.5.so
00b95000-00b96000 r-xp 00024000 08:01 21202154 /lib/libm-2.5.so
00b96000-00b97000 rwxp 00025000 08:01 21202154 /lib/libm-2.5.so
00b99000-00b9b000 r-xp 00000000 08:01 21202148 /lib/libdl-2.5.so
00b9b000-00b9c000 r-xp 00001000 08:01 21202148 /lib/libdl-2.5.so
00b9c000-00b9d000 rwxp 00002000 08:01 21202148 /lib/libdl-2.5.so
08048000-08148000 r-xp 00000000 08:11 9126124 /opt/sybase/OCS-15_0/
bin/bcp
08148000-08170000 rw-p 00100000 08:11 9126124 /opt/sybase/OCS-15_0/
bin/bcp
08170000-08174000 rw-p 08170000 00:00 0
08760000-08847000 rw-p 08760000 00:00 0 [heap]
b7d00000-b7d21000 rw-p b7d00000 00:00 0
b7d21000-b7e00000 ---p b7d21000 00:00 0
b7ee1000-b7f03000 rw-p b7ee1000 00:00 0
b7f13000-b7f15000 rw-p b7f13000 00:00 0
bfb15000-bfb2a000 rw-p bffea000 00:00 0 [stack]
Aborted

[Neal Stack [Sybase]]

Hello,

When using bcp with text columns you need to specify the maximum length
you expect your columns to be or else it will use ASE's default of 32768:
% isql -Usa -P
1> select @@textsize
2> go

-----------
32768

See the bcp "-T" parameter for details:
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.utility/html/utility/utility195.htm

If you are using the "-Y" parameter for client side character set conversion
and the data you are copying into the server is expanding, be sure to
account for that when using the "-T" parameter:
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sag1/html/sag1/sag1477.htm

Regards,
Neal

[joe]

Thanks for the quick reply I tried bcp out with 40960 and 32768
then I tried bcp in with both parameters but I got the same error. I
am using the -Y option. This is how it looks

bcp main.dbo.ISSUES out ISSUES_OUT_111509 -Usa -Ppassword -SPROD -t
"&*&1234" -Y -T 32768

bcp main.dbo.ISSUES in ISSUES_OUT_111509 -Usa -Ppassword -t "&*&1234" -
f test032.fmt -SPROD -E -m 100000 -T 32768

I tried importing the table into act and the copying and pasting the
data into the new empty table but when I paste the data I get the
identity error even when I run SET IDENTITY_INSERT ISSUES ON

any other ideas?

[joe]

Hi again looks like i found the solution, I am glad this only happens
for one table. I imported my issues table to act. I created a table
with a numeric field instead of identity then I copy and paste all
rows in access and then I did this

ALTER TABLE ISSUES2 ADD tmp numeric(7,0) NULL

UPDATE ISSUES2 SET tmp = ISSUE_ID

alter table ISSUES2 drop ISSUE_ID

alter table ISSUES2 add ISSUE_ID numeric(7,0) identity not null

exec sp_chgattribute ISSUES2, 'identity_gap', 1

set identity_update ISSUES2 on

UPDATE ISSUES2 SET ISSUE_ID = tmp

set identity_update ISSUES2 off

alter table ISSUES2 drop tm

what a headache, Ill be happy when my upgrade is done but for now I
really hate this database.

[Mark A. Parsons]

... snip ...

> what a headache, Ill be happy when my upgrade is done but for now I really hate this database

If you're a (relative) newbie to Sybase and/or bcp, then 'yes' it can be a bit of a headache *especially* when you also
add the requirements for bcp'ing text and identity columns *and* using different character sets between the client and
the dataserver *and* using a format file.

However, from your posts I get the impression that your frustration should be with not knowing how to use bcp as opposed
to any issues with the database ...

- first you post about an error concerning data truncation during character set conversions; it's not clear if your
error was generated by the 'bcp' utility program or a custom application using the bulk library API, whether the error
was generated during a bcp 'in' or a bcp 'out', the actual bcp/bulk-library options provided, the client and dataserver
character sets, the structure of the source and target tables, or the versions of the dataservers/bcp-utility/bulk-libaries

- then you post that you tried using different -T settings (40960, 32768) but got the same error, but your example bcp
commands lead to more questions ... what was the actual error message? what happens if you increase -T to something a
bit larger, eg, 100000? as you increase the size of the -T option, do you receive fewer errors? why did you use the
'-Y' option on the bcp 'out' but not the bcp 'in'? since you're bcp'ing out of, and right back into the same table, is
there a possibility that you received a duplicate key error? what's in the format file you used for the bcp 'in'?
since the bcp commands you provide show you doing a bcp from/into the same table, what happens if you forego the format
file in favor of the '-n' or '-c' option?

- but then you state that you tried 'importing the table into act' and got an identity error; what do you mean by
'importing' - were you using bcp? what is 'act'? what do you mean by 'copying and pasting the data into the new empty
table' - were you using a gui tool to move/copy data and if so, what gui tool? if you were using bcp to move the data
into the new empty table, how did you implement the 'set idenity_insert ISSUES on' command?

- finally, you mention that you'll be happy when your 'upgrade' is done but no details on what you mean by 'upgrade' or
what's being upgraded; are you upgrading the ASE/dataserver software and if so, are you upgrading the dataserver
in-place or migrating data from the 'old' dataserver to a 'new' dataserver? if performing an in-place upgrade, why all
the bcp's and copying/moving of data? are you upgrading/modifying your database structure and/or application? if
you're upgrading the dataserver via movement of data between 'old' and 'new' dataservers, what are the different ASE
versions and have the dataservers been configured with the same character sets?

---------------------

If you're content with where you are in your upgrade process ... no problem.

---------------------

If you'd like to figure out how to get the bcp's to work correctly, ie, fewer headaches ... say so. If so, a few
suggestions to get you started ...

- explain what kind of upgrade you're performing

- if upgrading an ASE dataserver, please provide the version string for both the old and new versions of the dataserver
software:

dataserver -v

- provide the version string of the bcp binary you are using:

bcp -v

NOTE: If you are using different installs of bcp (eg, different platforms, different ASE/SDK versions, then run this
command for each copy of bcp that you're using.

- if upgrading an ASE dataserver by copying data between old and new dataservers, please provide details about each
dataserver's installed character sets by running the following in both dataservers:

exec sp_helpsort

NOTE: If your plan is to change character sets during the 'upgrade' process, please state the desired source and
destination character sets.

- post the structure of the source and target table by running the following in each database for the table in question:

exec sp_help <table_name>

- find the largest text string in use in the source table by running the following in the source database:

select max(datalength(<column_name>)),max(char_length(<column_name>)) from <table_name>

NOTE: Run this query once for each text (or image) column in the table.

With this additional info the members of this newsgroup should be able to suggest some bcp command line options to help
minimize/eliminate your headaches regarding the 'upgrade' of this particular table.

[Michael Peppler [Team Sybase]]

You can use the -E option to bcp to tell it to import the identity data
values into the identity columns.

See
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility195.htm
for details.

Michael

[joe]

Thanks again for the reply I agree this text field implemetation is
cumbersome. I meant MSACCESS instead of ACT on my last post.

With
bcpmain.dbo.ISSUES in ISSUES_OUT_111509 -Usa -Ppass -t "&*&1234" -
SPROD -Y -E -m 100000 -n

I get

Starting copy...
CTLIB Message: - L1/O2/S1/N42/1/0:

blk_rowxfer(): blk layer: internal BLK-Library error: Data truncated

while doing local character set conversion. col = 13
Text/image field is larger than the maximum value. Data truncated.
Unexpected EOF encountered in BCP data-file.
bcp copy in failed

with -c

I get

CSLIB Message: - L0/O0/S0/N36/1/0:
cs_convert: cslib user api layer: common library error: The result is
truncated because the conversion/operation resulted in overflow.
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.
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.
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.
Unexpected EOF encountered in BCP data-file.
bcp copy in failed

but it seems to work with access so I will use access to upgrade this
2 tables. I have 40+ tables and there are only 2 that have problems.
So i guess It should be ok
I am upgrading from 12.5.3 to the lastest 15.0.3. The os is RHEL5.3.
Thanks for the help I will leave the problem unsolved! I will just
move on with my upgrade on sunday again. Another weekend working in
the office.

[mpeppler@peppler.org [Team Sybase]]

You can only use -n if you used -n for the bcp out operation, and if
you are staying on the same hardware platform.
If you do use -n, then you do not use the -t or -n flags, but you
probably should use the corrrect flag to set the max text size.

Michael

[joe]

Its been a month since my upgrade from 12.5.3 to 15.3. So far
everything seems ok. I was able to upgrade by restoring the 12.5.3
database into the 15.3 server. Then I bcp out all the tables because
the restore had mixed logs and data. After that I re installed 15.3
and bcp in all tables. I bet this is not the cleanest or most
beautifull way to go about upgrading a database but there was no data
corruption. My old Sybase central does not work very well with 15.3 so
I guess I have to download the new one. I am not able to generate the
ddl for tables.

[Mark A. Parsons]

re: generating DDL for tables ...

ASE 15 has a utility called ddlgen ($SYBASE/ASEP/bin) which is pretty good at reverse engineering the DDL for a database.

You can run ddlgen from the command line (ie, no need to use Sybase Central).

[joe]

On Dec 10, 12:28 pm, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:
> re: generating DDL for tables ...
>

Thanks I did use that utility but it did not work for tables. I
believe it is designed for view and similar structures.

[Mark A. Parsons]

I've got regularly scheduled processes running at a couple clients that (successfully) extract the DDL for all objects
... including tables.

Either a) you're providing the wrong set of command line parameters or b) you've run into a bug.

If you've got the time ... please post the entire ddlgen command that you're issuing, along with all of the associated
output (ie, DDL, errors, etc). Please also post the version string ('ddlgen -v').