FreeTDS Development Group

[Konrad J Hambrick <konrad AT payplus.com>]

James K. Lowden wrote:
> Konrad J Hambrick wrote:
> > We are using either freebcp or MS BCP.EXE to automatically
> > upload tens-of-GB of data every night on each of 20+ systems
> > and my vote is for freebcp to work EXACTLY like MS BCP.EXE.
>
> How do you handle errors?  Do you scan the error files?  Because bcp.exe
> does not reliably return an error when there's a problem.  

Yes, BCP.EXE and Windows CMD.EXE are BOTH (more than) lacking in
the area of error handling.

However, my project is to do nightly conversions from a pair of
legacy Payroll and HR systems for a subset of our 'early adopter'
customers into a new SQL Server-Backed System that is still in
Beta / Parallel Processing Mode.

Because one of the legacy systems runs on a variety of OS's, we
chose to write the data conversion package using vanilla /bin/sh
scripts enhanced by a few GNU tools.

Data Conversions are handled by gawk (I am a **_REALLY_** old
UNIX-Bigot and my data conversion library predates even Perl-4) <G>

To run the scripts on Windows Systems, we install MSys and then
the same vanilla conversion modules will run on Linux or Win32.

We do detect the OS so that we run BCP.EXE instead of freebcp
and OSQL.EXE instead of sqsh on Windows.

We do rely on BCP.EXE Return Codes plus as you guessed, 'the size
of' the BCP Error file, to tell us whether or not BCP.EXE failed
(freebcp reliably returns an Error Code for us but we use the same
test there for ease of maintenance).

We do run a pre-bcp test script to avoid BCP errors as much as
possible.  This process:

   1. Sorts the BCP .TXT files in Primary Key Order
   2. Tests for Dupe PKeys
   3. Tests for 'good data' (as far as SQL Server is concerned).

This approach allows us to abort the conversion and completely avoid
the BCP step if there are any errors reported by the test-module,
there is also a payback for pre-sorting the data by PKey.

The reason I whined about HINTS a few years ago is because if your
data set is already sorted by PKey, you can send a HINT to SQL Server
and it eliminates **_A_WHOLE_LOT_OF_** post-bcp Server Load (SQL Server
seems to slurp the BCP data, then it sorts it by Clustered PKey in the
background after BCP is done unless you send an ORDER( Seq1,..., SeqN )
HINT).

OTOH, if you send the ORDER( ) HINT and the Data is NOT Sorted by PKey,
the BCP process fails.


> FreeTDS is first and foremost an API library, and fidelity to those
> specifications (and observed behavior) is the stated objective of the
> project.  Your humble maintainer does not consider perfect compatibility
> of the utility applications, on the other hand, to be very important. 
> That said, if others do think it important and want to contribute toward
> that end, my role is to facilitate and incorporate such contributions.  

I would prefer 'correct' behavior (for example Error Reporting one can count
on) when the 'standard tools' are broken.


> In the instant case, we're discussing a very fine point: how should the
> bcp utility respond to a line in a file with a field too wide for its
> column?  We're already in the error domain.  It's the kind of thing the
> vendor might change between versions.  In fact, *did* change when ANSI
> compatibility was introduced.  Not only that: it's very fragile, because
> the batch system (bcp) depends on a potentially changing server-side
> environment it can't easily interrogate.   


BCP should fail on faulty data and the faulty record should NOT be inserted.

-- kjh