A good example of Trace Parser, Visual Studio and Excel
Prerequisite
Before analysing a trace file, you might need to create a database for trace data. When you do troubleshooting actions on any VMs lower than D365FO 10.X.X (i.e. AX2012, D365FO 7.X, D365FO 8.X), no database was pre-created for trace's data. You will need to register a new one. Please check here.
A good tip of using Excel to analyse INSERT INTO statement captured in a Trace file: Check here.
Now we are good to get ourselves into this beautiful example.
The beautiful example
The given case
I had a case which was solved by analysing a trace file. That case gave me a huge happiness so I would like to share with you all. This case required some skills of analysing the trace file, understanding the logic of D365 indexes, navigating through Visual Studio, using SSMS queries to find the things we need. So, I believe this is a very nice example yet simple enough for everyone to start learning how to use such tools, especially Trace Parser.
Issue description
When I tried to authorise some Adjusted Demand Forecast records, I failed. System threw one error saying The record already exists
.
Self issue-scoping
The error message was pretty straightforward. It can be understood as: Cannot create a record in table Demand Forecast (with its name in [AxDB].[ForecastSales]
) because the record already exists in such table.
Note
In fact, different records in D365FO can be distinguished by some different key values of them. Such key values are unique for each record. So, for example, 2 records might have exactly similar details of
Sales Order
orSales Quantity
but they can be deemed different if they have differentRecID
values.
In this case, due to some reasons, the system was trying to insert a new record into ForecastSales table with some values which are already existing in such table. And, thus, failed.
We need to find what is/are that duplicated value(s) and try to fix it (them).
Troubleshooting
Visual Studio: Checking indexes
First and foremost, for any "Record has been existed" issues, the very first thing that we would need to check is the indices of the tables because the indices are the ones which decide the duplication rule (Allowed or Not allowed).
I would need to find which values in ForecastSales
table do not allow duplicated values. This can be done by check ForecastSales.Indexes
using Visual Studio
.
Open Visual Studio
and look for forecastsales
table in the AOT.
Right-click on it > Open Designer
to open Table designer. Expand Indexes
(this is somewhat bugging me for a long time because the plural form of Index should be Indices rather than Indexes, yet never mind).
Choose View
(on Menu bar) > Properties Window
to open Properties window. Or, we can simply press F4
.
Check all indexes to see if any indexes DO NOT allow duplicate. Those should be the indexes that we should tackle.
After analysing, I found that two indexes which do not allow duplicate. They were ExpandIdx
and ForecastEntryNumberIdx
. Expand those indexes to find which fields are included. They were (1) the combination of ExpandID
and RecID
and (2) ForecastEntryNumber
.
We would need to take note these fields.
Trace parser: Analysing the trace file
Before analysing any trace files, we would need to have the ID of the session in which we captured the file.
Go to Question mark
on the upper-right corner of your browser > Feedback
> Find the session ID.
Open Trace Parser
from Start menu and Import
the trace file that we captured.
Select grouping with same Session
that we just took note.
Usually, we will need to analyse Call Tree, X++ and SQL included in the trace file to understand the issue's cause. The approach much depends on the issue. In my case, I would look into SQL queries because I assumed that an INSERT INTO
statement was made to the table ForecastSales
.
Switch to SQL tab and search for "forecastsales" (pay attention to the asterisks). This means we want to search for any queries containing "forecastsales"
no matter where the keyword is in the queries.
Luckily, I found one.
By analysing the INSERT INTO statement using Excel, I found the values that system tried inserting into ForecastSales
table:
ExpandID
= '0'RecID
= '5637233827'ForecastEntryNumber
= 'PAU-000063760'
We would need to memorise these values or write them down somewhere.
SSMS: Finding the duplicated values
Open Microsoft SQL Server Management Studio
from Start menu and log in with default Windows Authentication.
Open a New Query
window by choosing Databases
> AxDB
> New Query
.
I would execute 3 different queries because I wanted to look for duplicated values in 3 fields:
- ExpandID = '0'
- RecID = '5637233827'
- ForecastEntryNumber = 'PAU-000063760'
Type queries in the window > Press F5
or Alt+X
or Execute
button to execute the queries.
Examination:
ExpandID
: Hmm, quite a lot of records having a same value of '0', but when involving all these '0' with the uniqueRecID
, the combination should be different (we can actually double check this one using the queryselect RECID, count(RECID) as Number from FORECASTSALES group by RECID order by Number desc
, any Number >= 2 are the duplicated ones)RecID
: No records were found --> This means there should be no problem when inserting a new record with RecID = '5637233827' into this table.ForecastEntryNumber
: One record was found --> This means the system will throw such error "A record is existing in the table" when it tries inserting a new record because this field does not allow duplicate.
We found it.
D360FO client > Number Sequence
The solution for this particular issue should be simple: Find our way to the Number Sequence that is for ForecastEntryNumber and increase the Next
value by 1.
I showed no solution here because the main purpose of this post is to demonstrate the way that we can co-use such available tools to approach an issue. Yet, each issue requires more or less different approaches. I hope that some quick look into these might plant some hints and you all can develop your own way of using them.
Happy Troubleshooting!