SQLDMO.Bulkcopy diagnostics on failed load

[Nils]

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.
Thanks
Nils

[Erland Sommarskog]

Nils (de*@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[scoots987]

On Dec 9, 5:31 pm, Erland Sommarskog <[e-mail]:

Nils ([e-mail]) 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, [e-mail]

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

[Erland Sommarskog]

scoots987 (sc*******@gmail.com) writes:

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.

There is a sample command:

bulk insert fritte from 'C:\temp\slask.bcp'
with (datafiletype = 'char', fieldterminator = ',',
batchsize = 1, errorfile = 'C:\temp\slask.err')

BATCHSIZE = 1 is something you would only use for troubleshooting,
as it cut performance quite a bit.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx