[freetds] Re: Freetds freebcp

Lowden, James K LowdenJK at bernstein.com
Thu Jun 10 10:56:12 EDT 2004

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



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.

More information about the FreeTDS mailing list