thatoneguy.net

If you’ve worked with building a CLR trigger for MsSQL, then there are decent odds you’ve seen this come back to the message window while testing the trigger inside Microsoft SQL Management Studio:

The context transaction which was active before entering user defined routine, trigger or aggregate “[CLR trigger name]” has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.

So, after you finish pulling your brains out one hair at a time, do this:

1. Put a try { } catch(Exception as ex) { } around the block of code that is executing commands into the database
2. Use the SqlContext.Pipe.SendMessage(ex.Message) to send the message to the Management Studio message window
3. Figure out why your query is crashing and causing the entire transaction to rollback, therefore causing this problem.

As you can see, this problem arises when there is a problem with one of the queries being executed in the Transaction (you inherit the Transaction from when the trigger is invoked). When a trigger throws a rollback inside the transaction, it causes an exception.

I also tried to begin another transaction (inspired by the concept of “nested transactions”) but that didn’t seem to work. I have one other thing I plan on trying if this problem flares up again (which I have a feeling it will). I’m going to wrap every command text in a try catch block, for the SQL database. Since I have all of my commands accessing the database at a centralized method, this should be fairly easy to implement. I think this will prevent such a rollback, but havn’t tried it so I’m not sure. If you try it first, let me know!

Here’s some syntax for SQL Try/Catch statements:

This entry was posted on Wednesday, November 21st, 2007 at 6:43 pm and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.