Skip to Content.
Sympa Menu

freetds - RE: [freetds] Re: Freetds freebcp

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Re: Freetds freebcp
  • Date: Thu, 10 Jun 2004 10:56:12 -0400

> [mailto:freetds-bounces AT lists.ibiblio.org]On Behalf Of R. Klemme
> Sent: June 10, 2004 6:47 AM
>
> Well, the motivation is this: bulk loaders of other databases
> (namely a big
> one with an "O" ;-)) just send error lines to a log file and continue
> importing. So for reasons of simplicity and consistency I'd
> like to see the same behavior with MS SQL Server.

I see. BTW, afaik the Big O database servers don't have anything like bcp,
that is, no special protocol, no way to bypass the transaction log. If you
could point me to information about such a thing, I'd be interested.

For our part, rows with client-side conversion errors are filtered to the
error file and count toward the (-m) maximum error count.

> The
> interesting question is IMHO whether the bcp API in freetds allows to
> implement such a behavior with reasonable effort. I've
> browsed through the
> sources but unfortunately the bcp API functions still seem to be
> underdocumented, so It wasn't too easy to figure what's going
> on exactly (especially how error line indexes are reported).

You're right, and I spoke too soon. :-( A little experimentation and log
perusal reveals that when the server rejects the batch, it doesn't say which
row was the offender.

For such problems, you have two options. The clean one is to BCP to a
separate table, and "insert A ... select T ... where not in (select A ...)".
Defeats some of the advantages of bcp, but, like oatmeal, it's the right
thing to do. The dirty way is to use IGNORE_DUP_KEY when creating the index.
That will generate a 3604 message for each batch containing ignored rows,
but it still won't tell you which ones were ignored.

My experiments with Microsoft's BCP show that a batch containing duplicate
keys, when applied to a table whose index was created "with IGNORE_DUP_KEY",
is rejected in its entirety. freebcp OTOH doesn't respond that way; the
non-duplicate rows land in the table. I conclude Microsoft's utility
cancels the batch somehow.

> > There are other kinds of errors, mostly on the client side.
> There are
> > numerous opportunities for data conversion errors, short
> rows, invalid
> > nulls, etc. You'd have to cope with them, too, of course.
>
> Yes, of course. But as I understand these errors are
> reported but lines are
> otherwise ignored. I think I've seen a command line argument
> that denotes
> the max ignorable errors - or do I confuse that with MS's bcp?

Right. It seems we can say that client-side errors are detected per row (and
per column), but server-side errors are reported per batch (without row
information). The maxerror option thus applies to client-side (mostly data
conversion) errors.

I've done a lot of BCP work over the years, and have often confronted the
problem of loading data into a table when I don't know what's already there.
I never tried the backup-and-skip scenario, and now I know why. Always the
solution was to use another table and some SQL, or go the row-at-a-time
route. Them's the choices, because we can't force the server to give us the
row-level information. Not even with Donald Rumsfeld's help.

Regards,

--jkl









-----------------------------------------
The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above. If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the original
message. Please note that we do not accept account orders and/or instructions
by e-mail, and therefore will not be responsible for carrying out such orders
and/or instructions.
If you, as the intended recipient of this message, the purpose of which is to
inform and update our clients, prospects and consultants of developments
relating to our services and products, would not like to receive further
e-mail correspondence from the sender, please "reply" to the sender
indicating your wishes. In the U.S.: 1345 Avenue of the Americas, New York,
NY 10105.






Archive powered by MHonArc 2.6.24.

Top of Page