Hi.

I'm pretty much a newby in sybase.
what I'd like to do is to copy a table SDIPMS from db "A" to table
SDIPMS to db "B".

The tables have the same name but different data. In fact this copying
would only be inserting into SDIPMS in db "B".
Tables are defined as


CREATE TABLE dbo.SDIPMS
(
ID int NOT NULL,
MO char(16) NOT NULL,
CFAIL int NOT NULL,
MSNES int NOT NULL,
MSFES int NOT NULL,
MSNSES int NOT NULL,
MSFSES int NOT NULL,
MSNUAS int NOT NULL,
MSFUAS int NOT NULL,
MSNUAV int NOT NULL,
MSFUAV int NOT NULL,
SMI int NOT NULL,
MSNBBE int NULL,
MSFBBE int NULL
)

Tables also have indexes defined as:

CREATE UNIQUE CLUSTERED INDEX I_SDIPMS ON SDIPMS
(ID,MO)
WITH IGNORE_DUP_KEY
ON 'default'

What I'm trying to do is to bulk copy SDIPMS table from A into a file on
my unix machine and then bulk copy that file into SDIPMS table in B.

I'm getting message:
-------------------------------------------

Starting copy...
Negative length-prefix found in BCP data-file.
bcp copy in failed

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

Inserting data using queries is not an option as log is getting
overloaded as my tables have like several millions of rows.

Can someone help me with this, I'd be very grateful.

Thx, Koko

Koko Loko wrote:
Could you post the entire bcp command (with command line args) that you're
issuing?

Well, bcp'ing the data into the table isn't going to help you as far as
logging is concerned, ie, inserting via bcp will also create log entries
.... unless, of course, you're dropping your index(es) on the target table
and enabling 'select into/bulkcopy'???

Alternatively you could move small(er) batches of rows into the target
table, making sure to dump the transaction log more often.

--
Mark A. Parsons

Iron Horse, Inc.
iron_horse (AT) NOSPAM (DOT) compuserve.com

Hi.

Here it is:

/opt/sybase/sybase/OCS-12_5/bin/bcp csddbdb_temp..table_name in
../out_csddb/bcp_file_name -Usa -Psybase11 -SDEOMC1 -n

That's all I use. I know I can do it step by step with queries but
believe me, tables are HUGE.

Thx, Koko

Koko Loko wrote:
Curious ... what bcp command did you use to create this file?

Also, what OS are you running on and what is the size of 'bcp_file_name'?

Assuming you get the above bcp (in) command to work you're in for a rude
awakening:

- with indexes in place and/or 'select into/bulkcopy' disabled you will
incur a *LOT* of logging if this file is as big as you make it sound

- without the '-b' option to bcp you'll end up inserting the entire as one
big honkin' batch

In both cases if we're talking about a *LOT* of rows (enough to fill the
log with a 'normal' insert/select) then you'll fill your log using bcp,
too.

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

There's nothing wrong with using bcp but it's going to be slower than
issuing the appropriate insert/select commands ... and you'll *still* have
to worry about filling up your log.

On the other hand there's nothing wrong with a lot of logging if this is
necessary ... you just need to make sure you're dumping your transaction
log more often.

Either route you take (bcp or insert/select) make sure you keep your
transaction batches small enough so that they don't fill the log all at
once.

--
Mark A. Parsons

Iron Horse, Inc.
iron_horse (AT) NOSPAM (DOT) compuserve.com

Hello,

This looks like a very old bug. Bug numbers 195198/193111.
You may be able to workaround the issue depending on what
version of bcp you are using (type "bcp -v" to get the version):
http://www.sybase.com/detail?id=20439

You also might try bcping out in character mode (replace the
-n with -c). If you can use character mode, be careful if
any of your columns are text/image. If there are text/image
columns you must specify column and row delimiters that you
know will not exist in your data (for example -t"^~|" or -r "%^@").

- Neal

In article <498321de.0407161120.78c65706 (AT) posting (DOT) google.com>,
lucky_justin69 (AT) yahoo (DOT) com says...

Version:

bcp -v
Sybase CTBCP Utility/12.5/P/SPARC/Solaris 2.8/BUILD125-005/64bit/OPT/Sat

May 19 07:54:14 2001

System:

SunOS host_name 5.8 Generic_108528-27 sun4u sparc SUNW,Sun-Fire

Bcp out:
/opt/sybase/sybase/OCS-12_5/bin/bcp db_temp..table_name out
../out_csddb/bcp_file_name -Usa -Psybase11 -SDEOMC1 -n

Table has like 50,000,000 entries, log size is 1G. Data is like:

ID MO CFAIL TIMEINI TIMESUB OPSINI
OPSIAB OPSIREJ OPSSUB OPSAAB OPSAREJ OPRECAL
OPRECF ISTSEL ISTDERR
----------- ---------------- ----------- ----------- -----------
----------- ----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------
67370153 0 60 0 0
0 0 0 0 0 0
0 0 0 0

Content is numbers exclusively.

Thx, Koko

Hello,

That is the GA release of bcp which is about 3 years old.
I remember a bug (but can't find the bug number right now)
where bcp had problems when the last column in a table
allowed NULLs.

You could try working around this problem by altering your
table defintion such that you move the last two columns that
allow NULLs into the middle of the table definition:
CREATE TABLE dbo.SDIPMS
(
ID int NOT NULL,
MO char(16) NOT NULL,
CFAIL int NOT NULL,
MSNBBE int NULL,
MSFBBE int NULL
CFAIL int NOT NULL,
MSNES int NOT NULL,
MSFES int NOT NULL,
etc..
)

If that doesn't help I would recommend testing the latest EBF
(12.5 ESD#14 or 12.5.1 ESD#3 from http://downloads.sybase.com).

If after upgrading and/or altering the table definition doesn't
help you may be running into something new or have bad data.
You can use the first/last row flag to try and narrow it down
to specific rows of data (-F and -L).

Sybase technical support can help make that determination.

Thanks,
Neal

In article <498321de.0407190537.4ea817 (AT) posting (DOT) google.com>,
lucky_justin69 (AT) yahoo (DOT) com says...

Thx for helping .

In the end I decided to do it step by step, using queries with oftren
dumping transaction log. Slow but efficient.

Koko Loko