Error - The number of joins in the statement is 28. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack.

Table inheritance in AX 2012 is a nice feature, but it has to be used carefully due to the performance impact it might have. Putting the performance impact aside, this post is mainly focus on the error due to exceeded maximum table join, which is caused by Table inheritance and Replacement key.

The error message - "The number of joins in the statement is 28. This exceeds the maximum of 26. Turn on exception for this warning to see the call stack." (the 28 is due to the form has 28 table join and the 26 is a server settings for AOS, so these two numbers are not fixed, it depends on your form's datasource and server settings)

Brief info
- Forms\CustTable has an additional datasource. Eg. CustTableExtended
- CustTableExtended is InnerJoin to CustTable

A brief history of sample issue
While it is working OK the day before, the next day this error suddenly occur on Forms\CustTable. A further investigation found that the table join has increased due to a new field has been added to a table (Eg. CustTableExtended) which is InnerJoin with CustTable.

Cause of error
- The new table field on CustTableExtended has a relation to a derived/child table
- This derived/child table has a relation to the parent table
- On the parent table, it has a Replacement key.

When the CustTable form is opened, this additional field is joined to the child table, which is then joined to the parent table, this has caused an additional two table join, which happens to hit the limit of maximum table join.

Resolution
A. Consider redesign the solution

B. Remove Replacement key (usually won't choose this option due to presentable on form level is not user friendly - Eg. Showing RecId. See screenshot at the bottom of this post)

C. Increase the maximum table join (System administration > Setup > Server configuration > 'Performance optimization' tab > 'Performance settings' group > 'Maximum number of tables in join' field)

Simpler example
1. Current server settings: Maximum number of tables in join = 30

2. Tables\DemoBaseGroup
Parent table, has replacement key, SupportInheritance = Yes

3. Tables\DemoGroupA - Q (17 tables)
Derived/child table, extends DemoBaseGroup, SupportInheritance = Yes

4. Tables\DemoMainTable
Standalone table, , SupportInheritance = No, contain 17 fields (GroupA - Q), each field has a relation to its related table (DemoGroupA - Q) on RecId

5. Forms\DemoMainTable
Only 1 DataSource (DemoMainTable)

When the form is opened, it hit the maximum tables join error.
Given there's only 1 table in the DataSource, this error would first appears to be weird, but once you know field(s) with relation to inherited table which has Replacement key will cause an auto table join to the related tables, then it will make sense.

Below are the query generated when the form is run.
Two table join is created for each field with relation to inherited table with Replacement key.
Eg. 17 fields in the table will cause 34 table joins.


SELECT * 
FROM  DEMOMAINTABLE T1 
LEFT OUTER JOIN (DEMOBASEGROUP T2 INNER JOIN DEMOGROUPA T3 ON ((T3.DATAAREAID=?) AND (T3.RECID=T2.RECID))) ON ((T2.DATAAREAID=?) AND (T1.GROUPA=T3.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T4 INNER JOIN DEMOGROUPB T5 ON ((T5.DATAAREAID=?) AND (T5.RECID=T4.RECID))) ON ((T4.DATAAREAID=?) AND (T1.GROUPB=T5.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T6 INNER JOIN DEMOGROUPC T7 ON ((T7.DATAAREAID=?) AND (T7.RECID=T6.RECID))) ON ((T6.DATAAREAID=?) AND (T1.GROUPC=T7.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T8 INNER JOIN DEMOGROUPD T9 ON ((T9.DATAAREAID=?) AND (T9.RECID=T8.RECID))) ON ((T8.DATAAREAID=?) AND (T1.GROUPD=T9.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T10 INNER JOIN DEMOGROUPE T11 ON ((T11.DATAAREAID=?) AND (T11.RECID=T10.RECID))) ON ((T10.DATAAREAID=?) AND (T1.GROUPE=T11.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T12 INNER JOIN DEMOGROUPF T13 ON ((T13.DATAAREAID=?) AND (T13.RECID=T12.RECID))) ON ((T12.DATAAREAID=?) AND (T1.GROUPF=T13.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T14 INNER JOIN DEMOGROUPG T15 ON ((T15.DATAAREAID=?) AND (T15.RECID=T14.RECID))) ON ((T14.DATAAREAID=?) AND (T1.GROUPG=T15.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T16 INNER JOIN DEMOGROUPH T17 ON ((T17.DATAAREAID=?) AND (T17.RECID=T16.RECID))) ON ((T16.DATAAREAID=?) AND (T1.GROUPH=T17.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T18 INNER JOIN DEMOGROUPI T19 ON ((T19.DATAAREAID=?) AND (T19.RECID=T18.RECID))) ON ((T18.DATAAREAID=?) AND (T1.GROUPI=T19.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T20 INNER JOIN DEMOGROUPJ T21 ON ((T21.DATAAREAID=?) AND (T21.RECID=T20.RECID))) ON ((T20.DATAAREAID=?) AND (T1.GROUPJ=T21.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T22 INNER JOIN DEMOGROUPK T23 ON ((T23.DATAAREAID=?) AND (T23.RECID=T22.RECID))) ON ((T22.DATAAREAID=?) AND (T1.GROUPK=T23.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T24 INNER JOIN DEMOGROUPL T25 ON ((T25.DATAAREAID=?) AND (T25.RECID=T24.RECID))) ON ((T24.DATAAREAID=?) AND (T1.GROUPL=T25.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T26 INNER JOIN DEMOGROUPM T27 ON ((T27.DATAAREAID=?) AND (T27.RECID=T26.RECID))) ON ((T26.DATAAREAID=?) AND (T1.GROUPM=T27.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T28 INNER JOIN DEMOGROUPN T29 ON ((T29.DATAAREAID=?) AND (T29.RECID=T28.RECID))) ON ((T28.DATAAREAID=?) AND (T1.GROUPN=T29.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T30 INNER JOIN DEMOGROUPO T31 ON ((T31.DATAAREAID=?) AND (T31.RECID=T30.RECID))) ON ((T30.DATAAREAID=?) AND (T1.GROUPO=T31.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T32 INNER JOIN DEMOGROUPP T33 ON ((T33.DATAAREAID=?) AND (T33.RECID=T32.RECID))) ON ((T32.DATAAREAID=?) AND (T1.GROUPP=T33.RECID)) 
LEFT OUTER JOIN (DEMOBASEGROUP T34 INNER JOIN DEMOGROUPQ T35 ON ((T35.DATAAREAID=?) AND (T35.RECID=T34.RECID))) ON ((T34.DATAAREAID=?) AND (T1.GROUPQ=T35.RECID)) 
WHERE (T1.DATAAREAID=?) ORDER BY T1.RECID OPTION(FAST 5)

By removing the Replacement key, the error is gone and the query only has one table, but the form will show the RecId value itself only, which appears to be not user friendly/non-readable. So this usually won't be the solution. The best to do is either redesign the solution or increase the maximum table join.

One might think, why design a table with so many fields with relation to separate inherited tables? Bear in mind the demo above is just to show how the error occur, it wasn't a real life example, but on CustTable, that could easily be the real life example due to the massive table join in standard AX. 

The 'DirPartyPostalAddressView' in Forms\CustTable itself already has many table, together with other datasource, that's more than 10, if the form is further developed/customized (Eg. added one or two custom table and those table each has two or three field relation to inherited tables with Replacement key) then it would be easy to reach the limit.

Debug service operation in Dynamics AX 2012

Debugging standard X++ code is quite straight forward, open the code, set a breakpoint, run and debug.
But debugging X++ code running in CIL is different, it requires more work.

Service operation in Dynamics AX 2012 runs in CIL, you'll need to debug them from Visual Studio.

1. Open Visual Studio

2. Show the Application Explorer (View > Application Explorer)

3. Browse to the code you wanted to debug (Eg. the service operation)

4. Set a breakpoint

5. Attach the Visual Studio debugger to the Dynamics AX server process (Ax32Serv.exe)
    (Debug > Attach to process ...)

6. Click on the "Select" button and choose "Managed (v4.0) and click "OK"

7. Select the Ax32Serv.exe and click "Attach"
    (If you can't see this process, check the checkbox 'Show processes from all users')

8. Run the service operation process and when it reach the breakpoint, it will stop at the code in Visual Studio, you can then debug from there.


Setting breakpoint in Indo.add() or when Exception occur
In AX, one of the common technique to debug error is to put a breakpoint in Info.add(), this technique applies to service operation debugging as well (browse to Info.add() in Application Explorer, open the code in Visual Studio and set the breakpoint).

Another option would be to check the option for Exception.
(Debug > Exceptions ... > Check the checkbox for 'Common Language Runtime Exceptions')

Dynamics AX 2012 CIL debugging ref: http://msdn.microsoft.com/en-gb/library/gg860898.aspx


Remote debugging
The debugging of CIL doesn't always have to be done from the AOS hosting machine itself, it can be done remotely as well.

1. At the AOS hosting machine, go to "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Remote Debugger\x64"

2. Run the "msvsmon.exe" and leave it running there

3. On the remote machine (Eg. your laptop), start up Visual studio, follows Step 2 - 8 above, with an additional step at Step 5 - At the 'Qualifier', click on the Browse and select the AOS hosting machine.


Load Symbols
When you step in the code/method, if you're prompted to locate the code, you'll need to load in the symbols.

1. Go to: Debug > Options & Settings ... > Debugging > Symbols
2. Add this folder to cache: "C:\Program Files\Microsoft Dynamics AX\60\Server\[DynamicsInstanceName]\bin\XppIL\source"

*Change the [DynamicsInstanceName] to your instance name

Batch task doesn't run according to dependencies

During the testing of one of the batch job development, we occasionally encounter one weird problem, the batch job doesn't run according to the dependencies set on them (batch dependencies randomly not working).

We have a batch job, during runtime, it will create several batch tasks, among these task, some of them should run first and some of them will run concurrently, task dependencies are added during runtime.

We did a lot of testing, but occasionally when this happen, we have no idea what has cause it even though the job looks entirely alright, after spending some time on it, we raised a support call with MS Support, this issue has been reported before and it can be found at Partner Source support knowledge base.

Search for KB 2731538 or the subject line "Batch framework does not respect task dependencies in Microsoft Dynamics AX 2009 Service Pack 1", you'll need to download and apply the hotfix.

The screenshot below is showing the Task 3.1, has 3 dependencies, it should run after that dependent tasks are completed. But somehow, it disregard it, even though one of the Task 2 is still executing, Task 3.1 has start executing.

AIF - Object reference not set to an instance of an object

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
   <s:Body>
      <s:Fault>
         <faultcode>s:Client</faultcode>
         <faultstring xml:lang="en-GB">Object reference not set to an instance of an object.</faultstring>
      </s:Fault>
   </s:Body>
</s:Envelope>


There could be many reason for this error message, but this post is just to share the reason we hit this error.

Below are the details:

• We have a document service for a query (a main datasource with 3 sub datasource)
• At one of the sub datasource, among the field list, one of the field is causing the error shown above
• If this field is removed from the query, it works fine
• Further investigation found that this field has a property of Mandatory = Yes, EDT of RefRecId
• In the respective table, there's a relation for this field linking to another table's RecId
  Eg: SubTable.FieldA == RelatedTable.RecId
• RelatedTable is an inherited table
• At the parent table for RelatedTable, is has a replacement key

This error only happen if this mandatory field is == 0.

We tried testing it against normal mandatory field (Eg. a text field), even if empty (not filled in), the error didn't occur, it only occur to field that's linked to another table (and when the above conditions are true)