web
You’re offline. This is a read only version of the page.
close
Skip to main content

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) / Cross company freezes
Microsoft Dynamics AX (Archived)

Cross company freezes

(0) ShareShare
ReportReport
Posted on by dynamics developer 2

Hi, I am facing a strange issue when running the below cross company query

  while select crossCompany specTrans
            where specTrans.SpecCompany == _ledgerJournalTrans.company() &&
                specTrans.SpecTableId == _ledgerJournalTrans.TableId &&
                specTrans.SpecRecId == _ledgerJournalTrans.RecId
            join custTransOpen
                where specTrans.RefCompany == custTransOpen.DataAreaId &&
                    specTrans.RefTableId == custTransOpen.TableId &&
                    specTrans.RefRecId == custTransOpen.RecId
            join custTrans
                where custTransOpen.RefRecId == custTrans.RecId
            join custTransCustomer
                where custTrans.AccountNum == custTransCustomer.AccountNum


AX freezes when I run this. But when I run it without the crossCompany keyword it runs smoothly. I have verified that this query only returns one record.

the actual code can be found in \Classes\LedgerJournalTransUpdateCust\checkSettleVoucher

Any idea why this would happen.

*This post is locked for comments

I have the same question (0)
  • dynamics developer Profile Picture
    dynamics developer 2 on at

    Sure but it is standard AX code. I am not sure why they used crossCompany here

    Anyway my question is not about the code but how the crossCompany is causing AX to freeze?

  • dynamics developer Profile Picture
    dynamics developer 2 on at

    Thanks. let me try adding these.

    What version are you using?

    Mine is

    0451.1.jpg

  • dynamics developer Profile Picture
    dynamics developer 2 on at

    I think 2012 code should be better. But as I expected this change didn't help. It's something wrong with the environment because on DEV it's fine. On TEST it freezes. I already checked the data and all the joins are fine and returns 1 row from each table. I will try and run it on SQL now

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    Do you have DataAreaId literals enabled to avoid parameter sniffing for your SQL queries? In case you are using multiple company accounts with varying data sizes, it is a good practice to have it on. Unfortunately crosscompany selects involve multiple companies, for which the query processor engine will have a harder time to optimize this.

    As Sukrut said, you need to have SQL Server maintenance plans in place for fragmented indexes and you should regularly update statistics too.

    A "freeze" typically means that your SQL Server is busy, have you tried to capture the Query execution plan and see what is wrong with that? Having a field list list as mentioned above is beneficial for the actually used information. But the biggest boost always comes if you have no fragmentation, statistics are updated, and you have covering indexes created for the frequently used queries such as the settlements you have problems with.

    Please provide additional details of your findings for the mentioned areas.

  • Verified answer
    dynamics developer Profile Picture
    dynamics developer 2 on at 
    I am not sure if this was the real problem but we had same customers and vendors in muliple companies and the it was causing the freeze.

    So I added the last line below as a fix for both customer and vendor 

    while select crossCompany specTrans
   where specTrans.SpecCompany == _ledgerJournalTrans.company() &&
         specTrans.SpecTableId == _ledgerJournalTrans.TableId &&
         specTrans.SpecRecId == _ledgerJournalTrans.RecId
      join custTransOpen
         where specTrans.RefCompany == custTransOpen.DataAreaId &&
               specTrans.RefTableId == custTransOpen.TableId &&
               specTrans.RefRecId == custTransOpen.RecId
      join custTrans
         where custTransOpen.RefRecId == custTrans.RecId
      join custTransCustomer
         where custTrans.AccountNum == custTransCustomer.AccountNum
  &&  custTransCustomer.dataAreaId == _ledgerJournalTrans.company() //added

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 26

#2
Michel ROY Profile Picture

Michel ROY 14

#3
imran ul haq Profile Picture

imran ul haq 8

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans