<HiTech2k@discussions.microsoft.com> wrote in message news:efe32370-407d-4dea-9ae1-2b442de29855@discussions.microsoft.com...Dear all,
I am constantly running into this error when calling CLR functions from triggers (even when the triggers are CLR functions themselves): The context transaction which was active before entering user defined routine, trigger or aggregate "[name of CLR sp]" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
However, there is no cyclic nesting going on here. In fact, the error only appears when the CLR function is writing to the database. Only executing SELECT statements inside the statements doesn't freak out the SQL server (2005 SP1 in my case). I am not ending any transaction at all. This error shows up in many places: as exceptions from ASP.NET web pages, as errors in the msmerge_conflicts_info etc. Now I saw in the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=457818&SiteID=1
..that this was supposedly a known bug in CLR. But in my case, it appears that I can't call a single CLR inside a trigger if the CLR is supposed to write something to the database, which is a pretty severe limitation of CLR imho.
I was trying to figure out if I can avoid this error by escaping the transaction context, I tried that inside CLR (Using New TransactionScope(TransactionScopeOption.Suppress)) but it didn't work. Also, calling COMMIT TRANSACTION before calling the CLR doesn't seem to solve the problem since it's not suppose to end the ambient transaction of the trigger according to BOL, and in many cases it doesn't seem wise to do so, e.g. if the trigger is fired in merge replication as in my case.
Hi!
Your update from CLR proc could call some trigger that could ROLLBACK the transaction...
Could you try to produce a minimal repro for this
Thank you!
Hello,
I also have a problem with this exception. I created a CLR-trigger which uses a TransactionScope-object to ensure all INSERTS and UPDATES inside the trigger will be atomic.
I reduced the trigger to a minimum:
[
SqlTrigger(Name = "JobInstance executed", Target = "JobInstances", Event = "FOR UPDATE")] public static void JobInstanceExecuted(){
using (TransactionScope scope = new TransactionScope()){
scope.Complete();
}
}
As soon as I am instanciating the TransactionScope-object this error will occur.
Any help is appreciated.
Hi Daniel,
Thanks for posting. In my case, I am not using the TransactionScope object but your repro is far shorter than mine... anyway, let's see if anybody has a solution.
Hi!
As a general way of dealing with such problems, I can recommend inserting command select @@trancount here and there into your code. Such tracing will help you to find a place where trancount is dropped to 0 (which means rollback), or is increased or decreased (begin/end tran) unxepectably.
General rule: trancount must be same upon enering and leaving CLR SP (trigger etc.).
Please post your findings here; hopefully this will help me to repro the problem.
Hi Vadim,
thanks for your answer. But I think, this is not a problem on my side: I am using TransactionScope everywhere, so everything should be fine. If not, it would be a bug in the .NET-Framework...
Dear Vadim,
As soon as I make a write to the database inside the CLR trigger, the error occurs. Would that imply that the @@trancount drops to zero when I attempt to write If the CLR trigger doesn't write to the database, no error occurs. And if the T-SQL trigger starts another T-SQL trigger (where the second trigger writes), it also works. It's just that the CLR trigger UPDATE statement is targeting a table that has no triggers (so it does not chain up a third trigger).
/TW
Hi HiTech2k!
OK, let¡¯s start hunting for your problem. I understand that SqlCommand executing UPDATE throws. I believe, we want to look at what exception is thrown (it may be not the exception you mention, what you see may be a result of some exception replacement). Could you surround the throwing command with TRY-CATCH block and log the exception Something like this (I don¡¯t speak VB fluently J):
Try
cmd.ExecuteNonQuery ()
catch (System.Exception e)
LOG (e.ToString ())
End catch
You may also want to look if e.InnerException is not NULL and dump it as well. If these finding don¡¯t explain what is happening, could you publish them for me
Dear Vadim,
Thanks for the suggestion. I worked around this issue a while ago by re-creating the functions and triggers involved, in this way I could chain up the various triggers as desired (CLR or not CLR). It may have been that I had a cyclic trigger relationship that I could not identify for some reason. In any case I would try your debugging approach next time I receive exceptions before posting in the forums. Appreciate your help.
Best regards
/Toste