?
Solved

bad record causing incorrect insertion of next record into DB using bcp.

Posted on 2003-03-19
7
Medium Priority
?
242 Views
Last Modified: 2012-08-13
Hi,

I had a file with a few thousand records in it.
The file was bcp'd to a sybase DB upon which the bcp complained about 1 of the records.
It had one to many fields. The record was rejected and all other records inserted.

However, the record after the bad one was inserted incorrectly! Some fields values were wrong.

Any ideas as to why this may have happened ?

Regards,
Brian.
0
Comment
Question by:brianon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
7 Comments
 
LVL 4

Accepted Solution

by:
gletiecq earned 1000 total points
ID: 8168865
You may have a bad delimiter in the BCP file.  Check your text columns to see if they have escape sequences that will get translated into an additional field when they get BCP'd back in.  To work around this, you might want to change your delimiter to something you won't encounter in the text file.  Make sure that any delimiters you specify in BCP OUT will be specified in BCP IN.

Hope this helps.

Greg
0
 

Author Comment

by:brianon
ID: 8172829
Thanks Greg.

The file was not created with a BCP OUT, done by a unix process.
The delimeter is ','.
The record has an extra field ,x,(right after the 47) that got in there by unknown reasons.

677,35532,0,1,369258,,47,x,100000,0,330000,0,0,0,0,0,,,,0,0,0,0,1074
678,36412,0,1,369258,,47,10000,0,340000,0,0,0,0,0,,,,0,0,0,0,1074

So the bcp op complains about the first record but inserts the second incorrectly and the record should be fine.

Brian.
0
 
LVL 4

Expert Comment

by:gletiecq
ID: 8174607
I guess it's time to take a look at that unix process and fix whatever the bug is.  In the meantime, I suppose you can vi the textfile and manually fix the problem(s).  If you have a clean file, BCP in should work.

Greg
0
 

Author Comment

by:brianon
ID: 8174638
Actually, it was a solution to why the corruption on the insert I was looking for.
I can make sure the process doesn't do it again but, I think  :)
0
 
LVL 6

Expert Comment

by:rajesh009
ID: 8189220
Hi,

  The line terminatior normally appplied is \n. But your first record(bad record) might have had a line terminator somewhere in it. This might have caused the subsequent records to be inserted incorrectly. How does the second record look like in the table ?? That may help us in knowing the exact reason.

Thanks,
Rajesh.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
New style of hardware planning for Microsoft Exchange server.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question