Re: BCP In of data too large fails [NC]

Thank you James.

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.

-- kjh


James K. Lowden wrote:
> Cedric ROUVRAIS wrote:
>> If ansi warnings are set to off then the result of a bcp in should be a
>> truncation when the data is to large for the destination column.  Sybase
>> bcp works like this for sure, so I added the feature in freebcp.c
> 
> Microsoft's bcp.exe refuses to load a row with an overlong value
> regardless of the value of ANSI warnings.  Either way, you get this
> message:
> 
>   $ bcp testdb..a in a.txt -S mpquant -c -T
> 
>   Starting copy...
>   SQLState = 22001, NativeError = 0
>   Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> 
>   0 rows copied.
> 
> IMO this is the correct behavior, and I see no particular advantage to
> Sybase's choice.  Failure is *better*: with it, the user can edit the data
> file or alter the table, and consistently finds errors in the -e error
> file.  The change you propose forces the user to look (sometimes,
> depending on the database option) in the output log too, and the message
> doesn't bother to indicate which column is at fault.  
> 
> I'm not sure full compatibility with Sybase is all that desirable for
> freebcp.  For example, rather than truncating your data, wouldn't it be
> better if the user could specify a "rejected row" file?  freebcp could
> separate its input into to two piles: rows sent to the database, and rows
> not sent, for one reason or another.  The user could peruse the .err file
> (-e output) for problems, and modify & re-process the .rej file, knowing
> anything not in the .rej is already in the table. 
> 
> Another weakness of both the vendor and FreeTDS bcp utilities is that the
> don't always return an error to the OS if any row was not loaded.  That
> makes it useless for batch operations.  :-/
> 
> --jkl
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>