SQL Server JDBC Driver (redux)

So I’ve found a potential cause of yesterday’s problem: it has to do with how the JDBC driver handles the translation of <cftransaction> into actual SQL Server transactions.

A couple of tests on a dev server on my host have shown that if I limit the transaction to an update of a single table, this error never happens. As soon as my transaction wraps around the update of more than one table, the potential for this error opens up. Until now, I’d been lazy and simply wrapped my entire update script (which is adding/altering a half-dozen tables currently) in a single <cftransaction>, so it would commit the changes after the entire thing had run. Now, however, I’m ammending my code so that it commits the change after each table’s update has been done . And this makes sense, because its only a requirement that the update is successful within a given table, not everything at once.

There’s something just so satisfying about figuring out the solution to an ongoing bug. We’ve been experiencing this bug on 2 SQL Servers in particular at our host for the last 3 patches to The Pencilcase, while it’s worked without a hitch on others. I still don’t know the particular version of the JDBC driver on each of these servers, but I wouldn’t be surprised if the 2 that have caused bugs are using a different version. In any case, my thanks go out to the friendly tech support crew at CrystalTech, who finally gave me the clue I needed to solve this issue.