AX2012 AIF services error – The maximum number of joins allowed (99) is exceeded in the statement.
I recently came across an error which was causing a custom AIF service to fail in AX2012. When trying to execute a read on the service it was failing with an exception in the AIF exceptions form which contained the following text:
Stack trace: The maximum number of joins allowed (99) is exceeded in the statement.
The service query only contained 8 tables, so why it was hitting over 99 joins was a mystery.
Debugging the service cal in X++, we found that this was happening because in the service Axd class the method expandSurrogateForeignKeys() was set to return true. This meant that the query was expanded to cover all the surrogate foregin keys – it was reaching out to all the possible related tables for every table in the query.
The limit of 99 joins is the upper kernel limit, it cannot be exceeded. Setting a limit of 0 (in the server configuration form in AX client) will mean that 99 is used.
If you hit the same error in a different, non-AIF service, place it’s worth thinking about table hierarchies, as if you’re using tables in your query where they are part of a hierarchy then your query may contain more tables than you expect (to cover the reqired related tables in the hierarchy).
