HiTech2k


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.





Re: Strict Transaction Nesting Bug with CLR

MVP User


Can you post some code to show us what you're doing   Keep in mind that it's illegal to write anything to the database (using the context connection, at least) within a CLR UDF.
 

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
 
 
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.







Re: Strict Transaction Nesting Bug with CLR

HiTech2k

In the replication case, I have a trigger with this structure:
CREATE TRIGGER [dbo].[ProcessEvent] ON [dbo].[Evt]
FOR INSERT, UPDATE, DELETE
AS
-- Doing some other things
EXEC process_monit_event @newStatus, 1, 0, @evtID
UPDATE Evt
SET serverProcessed = 1
WHERE evtID = @evtID
END

This trigger is fired during the replication process since the table Evt is part of the merge publication and the subscribers sometimes insert new rows in Evt.

In the called, offending stored procedure, I have this structure:
CREATE PROCEDURE [dbo].[process_monit_event]
-- parameters here
AS
-- doing other things
EXEC dbo.CLRValidate @ID -- HERE is where the CLR is called.
-- if the above line is commented out, no error occurs
RETURN 0

The CLR looks like this:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function CLRValidate(ByVal entryID As SqlInt32) As SqlInt32
' doing other things
' Calling another CLR SqlProcedure here, which calls another CLR SqlProcedure,
' which writes to the database again, which causes the error.
' If the database writing queries e.g. UPDATE are removed, no error occurs.
Return RetVal
End Function

There could possibly be a more simple reproduction of the error.






Re: Strict Transaction Nesting Bug with CLR

Vadim Tryshev - Microsoft

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!






Re: Strict Transaction Nesting Bug with CLR

Daniel Hilgarth

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.





Re: Strict Transaction Nesting Bug with CLR

HiTech2k

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.





Re: Strict Transaction Nesting Bug with CLR

Vadim Tryshev - Microsoft

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.






Re: Strict Transaction Nesting Bug with CLR

Daniel Hilgarth

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...





Re: Strict Transaction Nesting Bug with CLR

HiTech2k

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





Re: Strict Transaction Nesting Bug with CLR

Vadim Tryshev - Microsoft

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






Re: Strict Transaction Nesting Bug with CLR

HiTech2k

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