X++ custom script

(6) Share

Report

Posted on by GhulamRasool

Hi,

I’d like to get an official clarification from Microsoft regarding a critical question that doesn’t seem to be explicitly documented anywhere.

Is it permitted to execute SQL kernel-level code within an X++ custom script deployable package for minor data correction activities in a Production environment?

I understand this approach is not recommended, but in some scenarios, converting the logic to pure X++ AOT objects results in significant performance degradation—particularly when temporary tables cannot be used (as they exist only within the original model). In such cases, a well-written and optimized SQL script can perform the operation far more efficiently.

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/deployment/run-custom-scripts

Could you please confirm Microsoft’s official stance on this?

Thank you,
Ghulam Rasool

Categories:

Microsoft Dynamics AX

I have the same question (0)

All responses (4)

Answers (1)

Sohaib Cheema 49,677 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi,

SQL execution is not recommended in general because one can damage the system data integrity for some of the following reasons.

1) it skips table AOS methods.

2) it skips the Delete Actions and other validation checks

3) it skips any associated events, methods, x++ logic and everything and leads to direct SQL execution.

People have made UI that directly executes the SQL queries on production, however that comes with the risk, as Microsoft does not support any issue occurred out of such executions.

Is this some update that you want to perform in standard tables or are these any custom tables where you want to update the records? What is your business scenario.

2 people found this reply helpful.

Was this reply helpful? Yes No
André Arnaud de Cal... 303,669 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi Ghulam,

You are asking for an official Microsoft statement. This forum is monitored by volunteers who are contributing with their experiences. Microsoft employees aren't usually providing support. If you need a statement from Microsoft, then contact a Microsoft representative or create a support ticket.

For sure it is not recommended to execute direct SQL. Sohaib provided some of the reasons. Can you explain your scenario? We can probably help you with an alternative approach.

2 people found this reply helpful.

Was this reply helpful? Yes No
GhulamRasool 18 on at

Like (0)

Report
Copy link

Link copied!
Hi Sohaib/Andre,

Thank you for your feedback. Let me explain my scenario below for data correctness:

Yes, we're inserting and updating in standard tables using joins and data from other standard tables.

Using X++ code, performs record by record operation since we can only work with one runnable class and no option to create temp tables otherwise, this specific scenario doesn't allow us to modify existing customer AOT objects and it is causing serious performance degradation while executing on the development environment, causing environment to go in non-responsive state,

Sharing a small part of code for both:

X++ SQL Code (Hugely performant)
str sqlUpdateDebit = @" UPDATE sa SET sa.DEBITACCOUNTEVENT = gjae.LEDGERDIMENSION FROM SubbillDeferralAuditTrail sa JOIN SUBBILLDEFERRALSCHEDULELINE s ON sa.SCHEDLINERECID = s.RECID JOIN LedgerJournalTrans ljt ON s.RECOGNITIONLEDGERJOURNALTRANS = ljt.RECID JOIN GENERALJOURNALENTRY gje ON gje.SUBLEDGERVOUCHER = ljt.VOUCHER JOIN GENERALJOURNALACCOUNTENTRY gjae ON gjae.GENERALJOURNALENTRY = gje.RECID WHERE gjae.ISCREDIT = 0 AND sa.SUBBILLDEFERRALAUDITTRAILEVENT = 2 AND (sa.DEBITACCOUNTEVENT IS NULL OR sa.DEBITACCOUNTEVENT = 0) AND s.SUBBILLDEFERRALSCHEDULENUMBER NOT IN (<list here>)"; updatedDebit = stmt.executeUpdateWithParameters(sqlUpdateDebit, paramMap);

X++ Code (Slow Performance)
// Update DebitAccountEvent while select forupdate auditTrail where auditTrail.SubbillDeferralAuditTrailEvent == 2 && auditTrail.DebitAccountEvent == 0 join s where auditTrail.SchedLineRecId == s.RecId join ljt where s.RecognitionLedgerJournalTrans == ljt.RecId join gje where gje.SubledgerVoucher == ljt.Voucher join gjae where gjae.GeneralJournalEntry == gje.RecId && !gjae.IsCredit { str schedNum = strUpr(s.SubbillDeferralScheduleNumber); if (!excludeSet.in(schedNum)) { auditTrail.DebitAccountEvent = gjae.LedgerDimension; auditTrail.update(); updatedDebit++; } }

Thanks.

Was this reply helpful? Yes No
Verified answer

Sohaib Cheema 49,677 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi,

Considering you are going to use such a custom script only once and not as repeated process, and using it at own risk, temporary table should not be an issue.

I can understand that you want to keep RecId(s) in a tamp table, as 1st step before doing mass update.

There are lot of Temp Table in the standard App. For example TmpAnalysis

Use any of those tables, which can keep your reference (RecIds)

2 people found this reply helpful.

Was this reply helpful? Yes No