The Case of the Disappearing Rows

I just lost a week of my life trying to track down a problem with a program that did a mass insert of records into a SQL Server database.  The program is written in Delphi 2007 and uses dbExpress components for access to SQL Server.

The problem first manifested after an 8 hour run when, lo and behold, the database only had 11K rows rather then the expected 884K.  I knew that the program was sound because I had just run the same program to load the same file into a Firebird database without a problem.  (That only took 3 hours by the way).

Now, the source file had some bogus data in it.  I knew this and the program made allowances for it by trapping the resulting exceptions, writing them to a log file and dropping the offending record.

It turns out that these data conversion errors fall into a class of SQL Server error known as “batch aborting” errors.  Whenever a batch aborting error occurs the current transaction is rolled back without so much as a “by your leave”.  You would think that this would be documented somewhere, in BIG BOLD LETTERS.  If it is, I can’t find it.  If it weren’t for Google I would still be scratching my head.

I found this table in an article on another web site:

Error Aborts
Duplicate primary key. Statement
NOT NULL violation. Statement
Violation of CHECK or FOREIGN KEY constraint. Statement
Most conversion errors, for instance conversion of non-numeric string to a numeric value. BATCH
Attempt to execute non-existing stored procedure. Statement
Missing or superfluous parameter to stored procedure to a procedure with parameters. Statement
Superfluous parameter to a parameterless stored procedure. BATCH
Exceeding the maximum nesting-level of stored procedures, triggers and functions. BATCH
Being selected as a deadlock victim. BATCH
Permission denied to table or stored procedure. Statement
ROLLBACK or COMMIT without any active transaction. Statement
Mismatch in number of columns in INSERT-EXEC. BATCH
Declaration of an existing cursor Statement
Column mismatch between cursor declaration and FETCH statement. Statement.
Running out of space for data file or transaction log. BATCH

The entire article, which is a good review of SQL Server error handling techniques, can be found here.

Leave a Reply