Question regarding bcp

[neerajrathi2 via DBMonster.com]

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.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/sybase/200807/1

[Manish Negandhi]

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.
> 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.

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

Manish Negandhi
[TeamSybase]

[SybaseNeal]

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

[Bret_Halford]

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.
> 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.

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