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.
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.
Comments
Post a Comment