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) / AX exec SP to return s...
Microsoft Dynamics AX (Archived)

AX exec SP to return success or failure

(0) ShareShare
ReportReport
Posted on by Ganriver1 1,559

Hi Friend,

I couldn't find a way to make stored procedure call to return a success or failure to AX. I don't need resultset, I just need to know it's success or not.

Then, I can go on my next step in X++.

Any suggestion?

Many thanks,

River

*This post is locked for comments

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

    It's possible to call direct SQL from AX, including stored procedures, but it's only the last resort when no native AX solution can be used. What are you doing that you thing what a stored procedure is your only option?

  • Ganriver1 Profile Picture
    Ganriver1 1,559 on at

    It's indeed last resort. We had old integration with web app. This SP is to get data from web app to staging table, then, AX code will import into AX from staging table.

    I just want to know SP is success or not to follow up with next step. Before, there is not even exception catching in place.

    I know you can do AIF, but, nobody wants to make big change.

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

    Does it mean that you wrote SQL code to connect to the web app and now you're looking for a way how to call it from X++? Isn't it much more complicated then getting data from the web app directly from AX? I think that simplifying your architecture will make your development, testing, debugging and deployment easier. I would do it.

    If you insist on calling the stored procedure from X++ anyway, use Statement.executeQuery().

  • Ganriver1 Profile Picture
    Ganriver1 1,559 on at

    Hi Martin,

    Web app is hosted on AWS, we were not allow to have direct access to it before, Data were transferred through XML file, then load into staging table.

    Now we are allowed to have read access, to minimized the impact, that's why we decide to use SP to load data into staging table,  without changing much of architect.

    Statement.executeQuery() is going to return a ResultSet, I don't need it. I just want to check if SP runs success or not. So I will know whether to proceed further.

    Thanks,

  • Verified answer
    jayh Profile Picture
    jayh 410 on at

    Have you tried statement.executeUpdate() instead of executeQuery()? There's also statement.getLastError() which should return 0 for success or an error code from the last SQL operation.

    Or you could use the resultset to return information from your SP to AX by having a "select 1 as result" or "select 0 as result" at the end of your SP depending on success or failure.

  • Ganriver1 Profile Picture
    Ganriver1 1,559 on at

    Thanks, Jay. getLastError() should be good.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    executeQuery() returns a value, which is what you asked for. If's up to you what you return, such as 1 or 0 depending on your business logic defining success and failure.

    Nevertheless it's a very weird way to import data to AX. You should take a look at the Data Import Framework, which supports both databases and XML files and you don't have to spend any time designing, implementing, testing and maintaining your own code.

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