When I first started out, something that would have helped me out a lot was to understand how to see the exact tables and SQL captured in Dynamics AX operations. Here I will share my own little recipe that I use on client sites to get SQL and database tables in a way that works with the Dynamics AX database framework. See, Dynamics AX heavily uses cursors which can often make it hard to see the underlying sql. This is just something that I cooked up to get past those barriers and still give us the information that we need to see.

.

First, open up SQL Server Profiler

Second, connect to the dynamics ax server

Choose File à New Trace

Then press Connect to attach to your new AX Server

Third, choose the SQL Server Tuning Template

Fourth, click on the events selection tab and add some other events

Now, on this part, we will use Brandon’s special Dynamics AX recipe. We need to add some events here. Now, what traditionally gets people in trouble is that AX uses a bunch of cursors, which don’t show the sql by default. However, I add 4 events to correct this and give me the information that I need.

Her I’ve added the CursorExecute and CursorPrepare events. CursorPrepare is the most important one because it shows you the sql and parameter values.

Now, you need to add 2 more events. Under Performance, add Showplan All and Showplan XML.

Now, just run your trace and watch what you get. Here is just one example where you can see the sql along with all the parameters. This is very, very helpful.