FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

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