External Datasource Run Stored Procedure

[tomdomurat (Thomas Domurat)]

I have been following this post since I have struggled getting external data from an Azure Database. The stored procedure lives in the Azure DB and has to be a stored procedure, not a just a Query. In the past, I used System.Data.SqlClient and the now the new Microsoft.Data.SqlClient – neither of which work as it seems that Epicor deprecated the ability to call external DLLs from a Function (according to them “a secuirty risk”). So, what is the definitive answer to call a SP in an external database and return a table dataset of values?

I am on 2022.2.x and get these types of messages:

Sytem.Data.SqlClient is not supported on this platform.
Microsoft.Data.SqlClient is not supported on this platform.

What I do not understand is that the external datasource uses the SqlClient Data Provider – so if that is the case, why did Epicor block the ability to use these external DLLs.

Error Detail

Correlation ID: 0350c543-689a-4c1d-b3d0-43d5bd180d3c
Description: BPM runtime caught an unexpected exception of ‘PlatformNotSupportedException’ type.
See more info in the Inner Exception section of Exception Details.
Program: Microsoft.Data.SqlClient.dll
Method: .ctor
Original Exception Type: PlatformNotSupportedException

[Mark_Wonsil]

@tomdomurat

Epicor deprecated the ability to call external DLLs from a Function (according to them “a secuirty risk”).

There is no online service that (intentionally) allows the ability to upload DLLs to their systems.

@tomdomurat

So, what is the definitive answer to call a SP in an external database and return a table dataset of values?

People use APIs.

[Mark_Wonsil]

And since your data is in Azure DB already, this would be a great opportunity to use Azure Functions.

How To Call A Stored Procedure From Azure Functions - Azure Lessons

[klincecum (Kevin Lincecum)]

@tomdomurat

I have been following this post since I have struggled getting external data from an Azure Database.

I’ve requested a split to a new thread.
We’ll see if we can help you there.

[klincecum (Kevin Lincecum)]

If you absolutely must call via System.Data.SqlClient, you can, but you’ll have to load
it dynamically through reflection.

I would encourage you to evaluate your other options first and see if there is something more
appropriate and “Epicory”

If you do end up needing assistance with the reflection, I can provide assistance.

[klincecum (Kevin Lincecum)]

I also have a question. Could you not use a trigger to pop your stored procedure, and define
that trigger in the external db to pop off on some write to the external source?

[tomdomurat (Thomas Domurat)]

Let me clarify that this in on-premises and not cloud. Also, the forms will most likely never be converted to Kinetics forms.

I am aware that there are technically better ways of doing this like a webservice API, but for now I just would like it to work like before without a complete re-write. Thanks, but no thanks Epicor for wanting to “protect us” but we can control security internally with permissions and firewalls.

I had tried the System.Reflection to force a load of the external assembly. It had compiled, but when I tried to use a DD with the Epicor Function I would get the “platform not supported”. I gave up on this as I spent too much time. If someone has some other ideas how to make it work, I am all ears.

[klincecum (Kevin Lincecum)]

Call store procedure in Kinetic Function not working

I helped @xvhelp privately because I thought he was doing something a bit different. Turns out he was not. He was calling a stored procedure in another database and just wanted to patch up his code to call it. Here is what we ended up with, since we just wanted to keep what he had going, without moving to a new library. //using System.Reflection Assembly System_Data_SqlClient = Assembly.Load("System.Data.SqlClient"); Type typeSqlCommand = System_Data_SqlClient.GetType("System.Data.SqlC…

[tomdomurat (Thomas Domurat)]

Right. I tried using that code and it did not work in 2022.2. It gave me errors.

[klincecum (Kevin Lincecum)]

@tomdomurat

It gave me errors.

Do tell. I know for a fact it works in 2022.2.

[tomdomurat (Thomas Domurat)]

Either from a Data Directive or a Data Directive calling an Epicor Function. It will compile and save, but throws me the error. I reported the issue to Epicor Technical Support and they tell me that external custom DLLs cannot be used. Supposedly, they tell me it is a “security risk”.

Server Side Exception

BPM runtime caught an unexpected exception of ‘PlatformNotSupportedException’ type.
See more info in the Inner Exception section of Exception Details.

Exception caught in: Epicor.ServiceModel

Error Detail

Correlation ID: 0350c543-689a-4c1d-b3d0-43d5bd180d3c
Description: BPM runtime caught an unexpected exception of ‘PlatformNotSupportedException’ type.
See more info in the Inner Exception section of Exception Details.
Program: Microsoft.Data.SqlClient.dll
Method: .ctor
Original Exception Type: PlatformNotSupportedException
Framework Method: A001_CustomCodeAction
Framework Line Number: 0
Framework Column Number: 0
Framework Source: A001_CustomCodeAction at offset 54 in file:line:column :0:0

Server Trace Stack: at Microsoft.Data.SqlClient.SqlConnection…ctor(String connectionString)
at EFx.GetWebService.Implementation.AzureSQLImpl.A001_CustomCodeAction()
at EFx.GetWebService.Implementation.AzureSQLImpl.RunStep(Int32 workflowStep)
at Epicor.Functions.FunctionBase3.Run() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.cs:line 92 at Epicor.Functions.FunctionBase3.Run(TInput input) in C:_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.cs:line 75
at EFx.GetWebService.Implementation.AzureSQLImpl.AdapterRun(ITuple input)
at Epicor.Functions.FunctionBase3.Epicor.Functions.IFunctionAdapter.Run(ITuple input) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.Adapter.cs:line 11 at Epicor.Customization.Bpm.CustomizationBase22.InvokeFunction(String libraryId, String functionId, ITuple input) in C:_releases\ICE\ICE4.2.200.15\Source\Server\Internal\Lib\Epicor.Customization.Bpm\CustomizationBase2.Plugins.cs:line 66
at Epicor.Customization.Bpm.DirectiveBase2.InvokeFunction(String libraryId, String functionId, ITuple input) in C:\_releases\ICE\ICE4.2.200.15\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.Plugins.cs:line 373 at Epicor.Customization.Bpm.DB.InTranDirective_AzureFX_6097811CDD3140B3B3A2002CF0EA9EA4.A001_CustomCodeAction() at Epicor.Customization.Bpm.DB.InTranDirective_AzureFX_6097811CDD3140B3B3A2002CF0EA9EA4.ExecuteCore(Int32 step) at Epicor.Customization.Bpm.DirectiveBase2.Execute() in C:_releases\ICE\ICE4.2.200.15\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 330
at Epicor.Customization.Bpm.DirectiveBase`2.Execute(TParam parameters) in C:_releases\ICE\ICE4.2.200.15\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 222

Client Stack Trace

at Ice.Cloud.ProxyBase1.CallWithCommunicationFailureRetry(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, RestRpcValueSerializer serializer) at Ice.Cloud.ProxyBase1.CallWithMultistepBpmHandling(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy)
at Ice.Cloud.ProxyBase`1.Call(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy)
at Erp.Proxy.BO.ABCCodeImpl.Update(ABCCodeDataSet ds)
at Erp.Adapters.ABCCodeAdapter.OnUpdate()
at Ice.Lib.Framework.EpiBaseAdapter.Update()
at Erp.UI.App.AbcCodeEntry.Transaction.Update()
at Ice.Lib.Framework.EpiSingleViewTransaction.Ice.Lib.Framework.IEpiAdapterLink.Update()
at Ice.Lib.Framework.EpiViewUtils.OnUpdate(EpiTransaction trans, EpiDataView view, IEpiAdapterLink link, Boolean displayExceptions)

Inner Exception

Microsoft.Data.SqlClient is not supported on this platform.