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
"one or more Rows do not belong to any partitions" and then it aborts bcp
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.
Could you create a transfer table that has the same fields as the main table but no partitioning. Then BCP into this new table rather than the existing table. Then select the good data out from the transfer table and insert it into your existing table. Obviously you’d want to report any bad data and you’d want to clear the transfer table before BCP’ing into it each time.
How much data do you hold? Is the partitioning by date necessary? - I thought the aim of partitioning would be to split queries across multiple partitions but all your data access would still be on the one partition assuming people are mostly interested in the latest data.
EDIT: I assume you're on Sybase ASE 15 where it allows you to split partitions by value - I haven't used this before so take this with a pinch of salt - just add an extra partition that takes the data outside your current range i.e.
date < current_min on my_new_segment
date > current_max on my_new_segment
As I say I can't find much info on partitioning by value. I assume there isn't a syntax like "else on my_new_segment" that you could add at the end. It seems odd they wouldn't provide for that.
Last edited by mike_bike_kite; 07-09-08 at 15:18.
Reason: new thought :)
Actually changing partition and other system leve details is not in my hand. Its just that our DBA has designed database and we have to use it.
My program collects data and fires bcp.Many times it gets error like some conversion has failed or data got rejected because of duplicate key(we have uniq key indexes built on all tables.). But in all of these case it ignores faulty record and load other records in file. It never happened that entire file got rejected because of single bad record in file.
I was just looking for some way to stop this happning in future.