Feature 'nested external environment calls' not implemented

[Former Member]

Hi.

I've created a .Net 3.5 DLL which is called by a stored procedure using CLR in a trigger.

Inside one of the functions I've got an update-statement against the same table as the trigger which calls the CLR code.

That update results in a SAExeption:

30.04.2014 14:19:55 (ConnID. 89) - W747lnr=4000996, Error: Feature 'nested external environment calls' not implemented

iAnywhere.Data.SQLAnywhere.SAException: Feature 'nested external environment calls' not implemented

   at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteReader()

   at CLRKalkyle.Exts.Execute(SAConnection conn, String sql, Object[] args)

   at CLRKalkyle.CalculusFunctions.DivInnfrakt(SAConnection conn, Pallet palle, Decimal totkvant)

   at CLRKalkyle.CalculusFunctions.GetInnfrakt(SAConnection conn, Int32 klientnr, String uttakstype, Int32 uttaksnr, Int32 w722lnr)

   at CLRKalkyle.Exts.DivKalkyle(SAConnection conn, Int32 klientnr, String uttakstype, Int32 uttaksnr, Int32 w722lnr, Int32 w747lnr)

   at CLRKalkyle.CLRKalkyle.GenKalkyle(Int32 klientnr, Int32 w747lnr)

I'm using SAServerSideConnection without transaction (the CLR is called per row in the database).

Is it possible to run the update in a new thread, or perhaps a new connection?

Or do I have to run the update after all the other code is finished?

Hope you understand my problem.

Regards,

Bjarne Anker

[jeff_albion · Accepted solution]

Hello Bjarne,

Could you provide the code for your .NET 3.5 DLL and the SQL trigger definition so we can better understand what you're currently seeing and trying to do?

Thanks,

Jeff Albion

SAP Active Global Support

[Former Member · Accepted solution]

This is the trigger:

ALTER TRIGGER "wau_w747_maaoppdat_on" after update of MaaOppdateres

order 2 on MTS.W747KalkyleData

referencing old as old_name new as new_name

for each row when(old_name.MaaOppdateres = 0 and new_name.MaaOppdateres = 1)

begin

    call spw_GenKalkyle_w747lnr(new_name.klientnr,new_name.w747lnr)

end

-------------------------

Stored procedure:

ALTER PROCEDURE "MTS"."spw_GenKalkyle_w747lnr"( in p1 integer,in p2 integer )

external name 'D:\\SVN\\Trading\\CLRKalkyle\\CLRKalkyle\\bin\\Release\\CLRKalkyle.dll::CLRKalkyle.CLRKalkyle.GenKalkyle(int,int)' language clr

-------------------------

This is where the exception occurs in the program:

sql = @"update w747kalkyledata w747

set w747.maaoppdateres = 1

from w722fakturalinje w722

where w722.KlientNr = ?

and w722.TilgangsType = ?

and w722.TilgangsNr = ?

and w722.tilgangsnr > 0

and w722.klientnr = w747.klientnr

and w722.uttakstype = w747.uttakstype

and w722.uttaksnr = w747.uttaksnr

and w722.w722lnr = w747.w722lnr

and w722.uttakstype = 'DF'

and w747.maaoppdateres = 0";

SAServerSideConnection.Connection.Execute(sql, palle.klientnr, palle.tilgangstype, palle.tilgangsnr);

-------------------------

In short, the trigger is at the table W747 and the code fails when running the update against the same table.

I guess it would be best to send the sourcecode via email if you need to look at the whole picture.

Regards,

Bjarne Anker

[jeff_albion · Accepted solution]

Hello Bjarne,

I have taken a further look at this issue and yes, I can reproduce this behaviour - this is a current limitation of external environments.

If you're triggering the external environment on a table, updates against the same table are not currently permitted - it is very easy to create a deadlock in this situation.

Furthermore while researching your issue, I discovered that attempting to create a new SAConnection from the SAServerSideConnection information will crash the server - I have opened this investigation as CR #764064.

If you need to make a secondary connection, you should create a new SAConnection from scratch with your user credentials and use that connection to perform updates (if you absolutely have to update the same table - but be very careful about deadlocks).

Regards,

Jeff Albion

SAP Active Global Support

[Former Member · Accepted solution]

Thanks Jeff.

That's the same conclusion we've made in our research.

I think we'll create a new function which will mimic the update, and do the necessary calculation without running the UPDATE against the table.

Nice to see that you perhaps have a solution for this sometime in the future.

Regards.

Bjarne