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:
|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.