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) / Batch job SQL Trace?
Microsoft Dynamics AX (Archived)

Batch job SQL Trace?

(0) ShareShare
ReportReport
Posted on by Konrad U 804

We have a problem in activating cost prices.  Interactively it works fine. In batch the SQL Server returns an error about bad syntax. Database has been synced and CIL regenerated.

I have turned on tracing for my id but I am only seeing the interactive statements not the ones in batch. Can anyone suggest the best, simplest way to view the bad statement?

Thanks

Konrad

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi Konrad U,

    You can use SQL Server Profiler to trace all queries on SQL side and Dynamics AX Trace Parser blog.saglobal.com/.../microsoft-dynamics-ax-2012-trace-parser.html

    I prefer to use AX trace parser because it gives you overview of x++ code and SQL statements as well.

  • Konrad U Profile Picture
    Konrad U 804 on at

    I tried that and it would probably work but I do not have an admin id, apparently you need the performance log group added.  Tried that and now the menu options from tools on over in the development or test environment has gone missing.

    Any suggestion on how to make those options come back or what they are predicated upon?

    Thanks

    Konrad

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    You user should be admin and  X++ development config key should be enabled. Also try to open client using "run as administrator" option.

  • Konrad U Profile Picture
    Konrad U 804 on at

    Sadly this did not help, have been doing development for quite awhile so that is not the issue. It did work briefly then went away. Strange that not only are the tools menu missing the other ones further on such as Windows and Help are missing as well.

    Might have something to do with remote desktop which does strange things at time in Dynamics. I guess a lot do not see this problem since they are probably are admin in the Active Directory security config as well as Dynamics admin. In the past working with reports has shown a similar problem where I can create and change but not deploy due to SSRS and Active Directory security.

  • Suggested answer
    Konrad U Profile Picture
    Konrad U 804 on at

    Turns out the inventItemPrice table has a method to lock the prior record while updating. That method was returning '1' for the SQL statement which obviously did not work. Comment out the lock statement and batch now works fine. With no mods in that area not sure that activating prices in batch can work  for anyone.

    The problem with running the trace is complicated. Two factors seem to be at work.

    1. Running the trace on the AOS server leaves the menu items out even with an admin capable id due to remote access. AOS server is virtual and headless.

    2. Remote access seems to exhibit erratic behavior with the tools menu. Sometimes it works other times it does not.

    So solved but it would be nice if documentation / bugs were resolved.

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi Konrad U,

    Can you post additional details for future references, like what is the AX method and what SQL statement you found?

  • Konrad U Profile Picture
    Konrad U 804 on at

    No problem. The method stdCostFindPrevLocked is in the inventItemPrice table.

    The statement is:

    statement.executeUpdate(sql);

    The trace showed the value of sql to be '1'. Now the price update in batch works fine.

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