web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / add sql function to ru...
Microsoft Dynamics AX (Archived)

add sql function to run an AX class.

(0) ShareShare
ReportReport
Posted on by Community Member

Hello,

How can I add a sql function (2012) to run an ax method class.

What is the concept?

 

Need some samples, please.

 

Thanks :)

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    What do you actual mean by "to add a sql function"? Do you mean to create a user-defined function in SQL Server that would somehow connect to AX?

    Please, try providing more information; I'm reminding you almost every time.

  • Community Member Profile Picture
    Community Member on at

    I am creating a sql function.

    I need a way to connect to call an ax dll and run.

    HTML is fine, since I can do some rest api tool that resolve any function in AX.

    Thanks :)

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    First think again whether you really want to do it. I don't know your scenario (since you didn't mention), but it may be another case when you chose wrong architecture.

    Then look at How to: Create and Run a CLR SQL Server User-Defined Function. And then follow the usual integration scenario from .NET to AX, e.g. using a custom service.

  • Community Member Profile Picture
    Community Member on at

    Thank you.

    It just checking out some abilities, so I can use that functions in some views, or whatever (in the sql-server).

    I did as following:

    1. Created new sql project as the link (I chose Visual C# CLR database object, choose server+database)

    2. Change the project framework to 3.5.

    3. Add user defined function:

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction()]
    public static SqlString testfunc()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};


    4. save+compile+deploy the project from Visual Studio (2010).

    5. open ssms in the relevant db, and type: select dbo.testfunc();

    6. Got the following error: 

    Msg 4121, Level 16, State 1, Line 2
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.testfunc", or the name is ambiguous.

    8. Farther, I need to run some AX code, but I don't get it, because the database server is not the aos server - How does the sql-server 'knows' to run functions in AX, whether AOS is not at the same server?

    The error I got in step 5 seems that function is not in db - What's wrong?

    Thanks :)

  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    "How does the sql-server 'knows' to run functions in AX?" Exactly as any other external code - you have to specify connection details. If you use a service, you'll provide endpoint URL. Are you sure that you chose the right architecture even if you don't know how it works?

    Regarding your deployment problem, check for any compilation and deployment problem. You also didn't mention enabling CLR on SQL Server, so I assume you forgot to do it.

  • Community Member Profile Picture
    Community Member on at

    I just need to figure what architecture is need to run ax functions from sql.

    I didn't know what architecture, and I needed some clues how doing above - If there are any better solutions than mine, I would like to know.

    Since I succeed doing the above, problem is solved.

    OK. I succeed running the function (finally) in sql server. doing

    sp_configure 'clr enabled', 1 ... etc.

    I couldn't deploy, and since deployment is just copy of the dlls, what I did:

    1. Put the dll in the assembly of sql server.

    2. Create a function like : create function testx()

    RETURNS int

    as

    EXTERNAL NAME SqlServerProject1.UserDefinedFunctions.testx

    (external name is the dll+class+function).

    And use it.

    3. For AX - I need to add interopted class and some classes, and I cannot put on server any dll (that's not work and many exception, such as:

    ...

CREATE ASSEMBLY for assembly 'Microsoft.Dynamics.AX.ManagedInterop' failed because assembly 'Microsoft.Dynamics.AX.ManagedInterop' is malformed or not a pure .NET assembly. 
Unverifiable PE Header/native stub. (Microsoft SQL Server, Error: 6544)
...


    4. So, I have checked ability running a web site (local server), and this was without problem.

    5. Since I know how using Rest-API, I can use that as an architecture and all the business logic will be on the AOS server (which I can run any function from ax) and getting i.e a json string etc - don't know whether this is the best approach, but it definitely solve the problem of running any ax-function from sql server.

    (Architecture that I have thought about is to put REST-API on the AOS server.

     Call the REST-API from user defined function - put that function on the server as mentioned.

     In the rest-api function do function like callaxfunc(type, objectname, methodname, parameters...). 

     All are internal servers I can use inside the organization - nothing is outside).

    Thanks, anyway :)

  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    Well, that you decided to implement your business requirement by calling AX from a custom-designed function in SQL Server is something that I called an architectonic decision. My point was that you should start from much higher level then restrict yourself to one particular way of implementation form the beginning. But it seems it's how you work.

    Anyway, I'm happy that my answers showed you how to implement what you asked for.

  • Community Member Profile Picture
    Community Member on at

    Nevertheless my solution, for some method I get exception.

    See post on blog: stackoverflow.com/.../security-issues-on-user-defined-functions-for-sql-server-2012,

    and I need to understand technet.microsoft.com/.../hh389774.aspx

    since SSRS report can call BL somehow - need understanding what is available in SSRS side to know what architecture I shall do.

    Thanks :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans