Hello,
I invited an architecture (seems a bit complicated one), that I can select an AX function as a culumn in SQL statment.
Something like:
Select dbo.displayMethod(tablename, methodname, dataareaid, recid) from ...
I used the following architecture (due some problems)
- Create A CLR function in SQL server that call a web site (I didn't find a way calling WCF directly from SQL server. I think I shall install some components for that).
- In the SQL server I add a new assembly, which is linked to dll of CLR function (Permission set: unrestricted - For that I need some privileges declaration on the server), and some function sthat connected to assembly.
- The web site is on AOS server, which is best on REST API (MCV4 in VS 2010 - need to install that, since it for VS 2012 and above usually).
The REST API function has get method that call a service in AX. The method returns always a string, which is translated on the SQL server (in the functions - date, string, numeric, etc.)
- There is unexplained problem (maybe some timeout declarations), that I created an instance of the WCF in the REST API function - and it stays available, so I need to create once, but sometimes (timeout?) the instance is not exist no more (even not null), so I run into exception, and need to create a new instance again and again - That's time consuming.
- The WCF service on the AX is based on class that uses reflection (sysTable, sysDictionary, etc), and return the result to REST API function, which return that to string, and return back it to SQL server.
Some thing:
- There maybe needless point (maybe I don't need the web site and REST API - just calling directly to WCF, but I didn't find a way doing that.
- There is overhead (and time consuming) when I am falling into exception in REST API as described.
- Don't know what technique is Microsoft supported - I need doing that much as best practice let me.
- Is there other way doing that? Anyway that Microsoft provided?
Thanks :)
*This post is locked for comments
I have the same question (0)
