Re: SQLDMO.Bulkcopy diagnostics on failed load

Nils (dev@xxxxxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()
    ... the ODBC source using named pipe, but I don't know how to enable named pipes ... If it is not working I am going to use ado to connect to sql server. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.connect)
  • Re: [PHP] ODBC and long text fields
    ... We've a PHP app that uses ODBC to talk to a MS SQL server. ... The internal code for a query is ...
    (php.general)
  • Re: Strange Problems with ODBC connection to SQL Server
    ... When accessing Jet data, use DAO. ... This way you are not exposing connection information in clear ... >accesses an SQL Server 2000 repository via ODBC data source. ...
    (microsoft.public.data.odbc)