I'm having the following very perplexing and frustrating problem: I'm trying to programatically put large chunks of mixed-type data from multiple tab-delimited text files into an SQL Server database; because the data has various "landmines" and needs to be inserted in two distinct steps (one chunk gets fastinsert-ed into one table, then that table is queried for the new primary key (PK) values--which the DB automatically propagates into a second table--and the second table gets update-d with a PK-ordered second chunk), I've found it necessary to turn AutoCommit off, so that if/when problems arise in the second step, the first step can be rollback-ed; so as not to lose too much work, however, I have a commit placed at the completion of each file. So, the workflow looks like: dir for list of files to loop over; read/parse data from a file; open database connection and set AutoCommit to off; fastinsert chunk one; get new PK values; update with chunk two; check for success: if successful, commit, else, rollback (and add the name of the file to a list of "problem" files); close the database connection to try to enforce the commit (I added this step when I first detected that commit appeared not to be working); repeat with next file.
The problem is, rollback is rolling back everything, not just the changes since the last commit! I suspected a bug, so I tried to reproduce w/ a "minimal sample," i.e., insert one record, commit it, rollback, check state, but when I did this, commit works "as advertised." Thus, to "first order," the problem appears to be correlated to the size of the change that needs to be committed, e.g., I suspect that perhaps I'm issuing the rollback before the commit has had a chance to finish doing whatever it is that it does. (I know that chunk size is an issue on some level because the fastinsert-s take on the order of a minute or three and the update-s take on the order of 5 times that long. Of course, my "minimal sample" is rather less complicated than the full code, but really all the essential steps are reproduced, so I'm hard pressed to identify what my full code is doing differently...)
Anyway, two days in a row now I've set my code to chug away overnight, expecting to come in the next day and find everything moved, only to come in, find that it hit another landmine, rollback expecting only the pending file insertion to be undone, only to find that everything (we're talking over 220,000 58-column records this last time!) is lost--including me!
Any ideas as to what the hell is going on?
Thanks!
DG
