On Dec 9, 5:31 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Nils (d...@nils-dehn.de) writes:
> > I use SQLDMO.Bulkcopy in an VB6 (have to) program to load data from a
> > plain text file into a SQL Server 2000.
> > One of the target columns is NOT NULL but it happens that I receive a
> > missing value for that column in the source file. BulkCopy then has an
> > ODBC error raised that complains about the violated NOT NULL
> > constraint. The Bulkcopy error file however is empty. Is there a way
> > to figure out in which row of the source file the error occured ?
> > Maybe a call to some ODBC diagnostics method to receive further
> > information ? I'd like to avoid the obvious solution to drop the
> > constraint and then query the loaded data for NULL values.
>
> The common approach it to use a staging table and move on from there,
> but that is of course an extra that you may want to avoid if this
> happens rarely.
>
> It seems that NOT NULL errors are not logged in the error file. Furthermore,
> the appear to cause bulk load to terminate directly. (I tried command-
> line which uses ODBC as well.) You could set the batchsize to 1,
> in that case the bulkload will load all record up to the erroneous
> record. (But beware that a batchsize of 1 can severely affect performacne
> for large files. If you are loading into a new table without indexes,
> this can also lead to a disk explosion.)
>
> If this is a one-off, try using BULK INSERT instead. When I tested, I
> got a clear error message which said:
> "The bulk load failed. Unexpected NULL value in data file row 3, column 1.
> The destination column (a) is defined as NOT NULL."
> Furthermore, when I used BATCHSIZE = 1, all rows but the bad one was
> loaded. I should hasted to add that I did all these tests with SQL 2005,
> but I would expect SQL 2000 to be the same.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, how do use BATCHSIZE = 1? I don't have SQL server here right
now but wonder how to use BATCHSIZE. I use BULK INSERT. MAN! is it
quick.

TIA