Question regarding bcp

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

neerajrathi2 - 09 Jul 2008 12:32 GMT

Hello,
We use bcp to populate data in tables. Tables have partition for each week.
Problem is sometime by error we get Future's data. So while bcp throws error
that
"one or more Rows do not belong to any partitions" and then it aborts bcp
command.
because of none of records in file get loaded.

My question is there any way to stop this so that bcp will ignore that record
but process other records.
I tired -m option of bcp but got same result.

Thanks in advance.

Reply to this Message

Manish Negandhi - 10 Jul 2008 14:17 GMT

On Jul 9, 4:32 pm, "neerajrathi2 via DBMonster.com" <u37674@uwe>
wrote:

> Hello,
> We use bcp to populate data in tables. Tables have partition for each week.
[quoted text clipped - 7 lines]
> but process other records.
> I tired -m option of bcp but got same result.

What version of ASE are you using ? can you post the exact error
message with message number ?

Manish Negandhi
[TeamSybase]

Reply to this Message

SybaseNeal - 10 Jul 2008 18:18 GMT

Hello,

Bcp works by sending rows to the server in batches (for performance
reasons).
If one row fails in a batch, than the entire batch fails. So if you
set your batch
size to one you should be able to copy in all of the rows that belong
in partitions:

% bcp pubs2..segtest in test.dat -Usa -P -c -b1

Starting copy...
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Server Message: NSTACKSUN - Msg 4851, Level 16, State 2:
BULK COPY IN failed. One or more rows do not belong to any partition
of table 'segtest'.

Server Message: NSTACKSUN - Msg 3621, Level 10, State 0:
Command has been aborted.

Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
Batch successfully bulk-copied to SQL Server.
bcp copy in partially failed

23 rows copied.

Setting the batch size to 1 causes Adaptive Server to allocate one
data page to one row copied in. This option only applies to fast bcp,
and is only useful in locating corrupt rows of data. Use -b1 with care
— doing so causes a new page to be allocated for each row, and is a
poor use of space.

Thanks,
Neal

Reply to this Message

Bret_Halford - 14 Jul 2008 18:07 GMT

On Jul 9, 5:32 am, "neerajrathi2 via DBMonster.com" <u37674@uwe>
wrote:

> Hello,
> We use bcp to populate data in tables. Tables have partition for each week.
[quoted text clipped - 9 lines]
>
> Thanks in advance.

Have you considered adding a partition for future data so the data has
a place to go?
You could run a job after the bcp to delete that data.

-bret

Reply to this Message