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.