T-SQL Error Handling Part 2

Well, I handled my own error! I went back to the exercises, and executed just part of the second exercise that had an issue with completion, and it swiftly completed. Confused but pleased, I executed the entire second exercise and it completed. How can that be, since it didn't complete yesterday?

Then, I noticed the window with the first exercise was disconnected from the database since I'd put my computer to sleep last night, and I read through the code again. It didn't end with a ROLLBACK or COMMIT, so while it showed an error, the transaction was still running. When I tried to run the second exercise, it was still waiting on the first to complete since I was updating the same table. I tested my hypothesis, ran the first exercise, ran the second exercise, and ran sp_who2. Just as I thought, the second exercise was being blocked by the first. I killed the session that held the first exercise, and the second exercise completed. I added ROLLBACK to the first exercise, it completed, and when I ran the second exercise, it too completed. Finally, a successful set of exercises, and I learned more than just the error exercises themselves!

With that issue solved, I went on to the next set of exercises using XACT_ABORT along with THROW. Those exercises went smoothly, so today I'll be trying the TRY/CATCH exercises, then move on to the next section, Managing Transactions and Concurrency.

At work, I was able to read and fix an issue that I'd only briefly dealt with about three years ago dealing with a stored procedure that dynamically created synonyms in a database that point to a different server and database. It ended up being very simple, but I had to read through and figure out what it was doing to properly diagnose and solve the issue. Happily, it only took about 30 minutes.


Popular posts from this blog

Board games and near the end of the gym challenge

December Challenge has ended!

Surprising success with Ham and Bean soup