Sybase bcp error

database sybase sap-ase bcp bulk-load
andreih picture andreih · Aug 7, 2013 · Viewed 12.6k times · Source

What i want to do is copy a table into a file, truncate the table and copy the data back into the table.

For this, i am using the following two commands:

Out: bcp TABLE out file.csv -S SERVER -U user -P password -r '\n' -t '^|' -c
In: bcp TABLE in file.csv -S SERVER -U user-P password-r '\n' -t '^|' -c -J iso_1 -b 5000

This is the error 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.

The interesting part ( for me, at least ) is that i get the error only for rows with the first column being an ODD number. From the first 3 million rows, it cuts half of them, all having the first column ( the PK ) an odd number.

I tried with different options, but none seem to work: no problem with the charset as far as i can tell, there are no huge columns such that they are truncated and it is not the carriage return missing.

Any help would be greatly appreciated.

UPDATE: After creating a format-file there are no more errors, but it only copies half of the data back into the table.

UPDATE: I managed to create a format file which works and loads all data, but i cannot use it on another server (it works in testing environment, it needs to run in production environment), since it says Attempt to read an unknown version of bcp format-file.? I know what this means, but is there any way of finding the correct values of the version?

SOLVED: After digging back in the database, it seems that the problem was indeed data inconsistency due to the fact that the VIEW used in production to copy the table only copied 25 columns, but the table has 26 columns ( somebody altered the table and i didn't know and hadn't noticed that it happened ). Fixed the View and now it works.

Answer

Michael Gardner picture Michael Gardner · Aug 7, 2013

Since you are going out of/into the same server, I recommend you use bcp with the native flag.

bcp DBNAME..TABLE out file.bcp -SSERVER -Uuser -Ppassword -n
bcp DBNAME..TABLE in file.bcp -SSERVER -Uuser -Ppassword -n -b5000

Character mode can get wierd, and I only use it when it is required.

Related questions

Sybase date comparison - Correct format?

I'm pretty new to Sybase and am writing a query to return results after a specified date, and also before a specified date. MM/DD/YYYY format At the moment im doing.. SELECT * From aTable WHERE afterDate >= 08/07/2013 AND beforeDate &…

Read More
SQL SELECT INSERT INTO Generate Unique Id

I'm attempting to select a table of data and insert this data into another file with similar column names (it's essentially duplicate data). Current syntax as follows: INSERT INTO TABLE1 (id, id2, col1, col2) SELECT similiarId, similiarId2, similiarCol1, similiarCol2 FROM …

Read More
How to get sybase table column name and its datatype and order by?

There are multiple table in my sybase database. I want to know the column name and datatype of a given table like (myOrder table). How can I do this? Below script I found on stackoverflow From a Sybase Database, how …

Read More