Executing Direct SQL Statements at Runtime in Dynamics 365 Finance & Operations

When working with Dynamics 365 Finance & Operations (D365FO), the standard approach to data access is through X++ query classes — select statements, QueryRun, SysDa, and the like. These abstractions handle field mappings, security, cross-company filtering, and valid-time-state logic automatically. They're the right tool almost every time.

But "almost" isn't "always." There are cases where you need to step outside those guardrails and execute raw SQL against the runtime database: bulk data corrections during a migration, performance-critical operations that the query framework can't optimize well, or diagnostic tooling that needs to inspect metadata tables directly. For those situations, D365FO exposes a set of classes that let you run SQL statements from X++ code.

This post walks through building a reusable utility class that wraps that capability cleanly, with proper error handling and a clear contract.

Why You'd Want This (and Why You Usually Wouldn't)

Before we write a single line, some honest caveats. Direct SQL in D365FO bypasses several things the framework normally does for you:

• Table-level security and record-level security (RLS) are not enforced.
• xRecord events (onInserting, onUpdated, etc.) do not fire.
• Database logging and change tracking won't capture the operation.
• Valid-time-state filtering is your responsibility.
• Cross-company isolation is your responsibility.

If any of those matter for your use case, stay with the standard query framework. Direct SQL is a power tool, not a daily driver.

Modifying system fields value while exporting data entities in D365 F&O

System fields like CreatedDateTime, ModifiedBy, and DataAreaId are read-only by default during export. Here's how  to override them.

Every table in D365 Finance & Operations has a set of system-managed fields — CreatedDateTime, CreatedBy, ModifiedDateTime, ModifiedBy, DataAreaId, Partition, and RecId. The kernel populates these automatically on insert and update. You never touch them in normal transactional code.

But data migration is not normal transactional code.

When you're exporting data from one D365 environment and importing it into another — or feeding it to an external data warehouse — you often need those system timestamps and user IDs to travel with the record. An audit trail that shows every record as "created by Admin at 2026-05-14 09:00" because the import job ran at 9 AM is not an audit trail at all. Similarly, when importing historical data, you may need to set CreatedDateTime to the original value from the source system.

This post covers how to expose, modify, and control system field values during data entity export and import in D365 F&O — the right way.

In this article

1. Understanding system fields and their default behavior
2. Exposing system fields on a data entity
3. Modifying system field values during export
4. Security and audit considerations

How to reduce database calls in X++ for Dynamics 365 Finance and Operations

How to reduce database calls in X++

Every unnecessary round-trip to SQL Server costs you latency, locks, and user patience. Here are the patterns that actually move the needle.

If you've ever watched a batch job crawl through thousands of records while CPU usage sits at 2%, you've met the N+1 problem. The logic is fine. The data is small. But every iteration fires another SELECT to the database, and those round-trips add up to minutes — sometimes hours.

D365 F&O runs on Azure SQL, which means every database call carries network latency on top of query execution time. A single call might take 1–3 milliseconds. Multiply that by 50,000 records in a loop, and you're looking at an extra 50–150 seconds of pure waiting — not computation, just waiting for packets to travel between the AOS and the database.

This guide covers eight concrete patterns for reducing those calls. Each one includes the before-and-after X++ so you can see exactly what changes, and where the performance gain comes from.

In this article

1. Replace row-by-row updates with update_recordset
2. Replace row-by-row inserts with insert_recordset
3. Eliminate N+1 selects with joins
4. Use exists join instead of inner select
5. Apply firstOnly for single-record lookups
6. Cache reference data properly
7. Use temp tables for complex staging
8. Batch your deletes with delete_from

Exposing Custom Business Logic to OData with SysODataActionAttribute in D365 F&O

One of the most common integration requirements in Dynamics 365 Finance & Operations is the ability to trigger custom business logic from an external system — not just read and write records, but actually do something: post a journal, recalculate a price, validate an order, kick off a workflow. The standard OData CRUD operations on data entities don't cover this. That's where SysODataActionAttribute comes in.

In this post, we'll look at what SysODataActionAttribute is, when to use it, how to implement it, and how to call it from an external client.

What is SysODataActionAttribute?

SysODataActionAttribute is an X++ attribute you apply to a method to expose that method as an OData action. An OData action is essentially a callable operation — a remote procedure call surfaced through the same OData endpoint that serves your data entities. Once decorated, the method becomes invokable over HTTP by any system that can authenticate against your D365 environment.

This lets you expose bound or unbound operations such as:

• Posting or confirming a document
• Running a calculation and returning the result
• Triggering a batch job
• Performing a validation and returning a status
• Any custom logic that doesn't map cleanly to create/read/update/delete

The attribute signature

The attribute takes two parameters:

SysODataActionAttribute(str actionName, boolean isStaticGlobalAction)

• actionName — the name the action will be exposed under in the OData metadata. This is what external callers reference.
• isStaticGlobalAction — a boolean controlling whether the action is unbound (a global, static action not tied to a specific entity instance) or bound (an instance action that operates on a particular record).

Setting Up a Unified Developer Environment for Dynamics 365 F&O: A Complete Walkthrough

 If you've ever stared down the task of provisioning a brand-new Unified Developer Environment (UDE) for Dynamics 365 Finance & Operations, you know it involves juggling multiple portals, precise installation sequences, and a few configuration steps that aren't exactly obvious. This post walks through the entire setup process — start to finish — so you can save time and avoid the most common pitfalls.

Whether you're onboarding a new developer or refreshing a stale environment, the steps below cover everything: environment provisioning, app installation, Dev Box setup, Visual Studio configuration, Git integration, model deployment, and database operations.

In This Post

1. Requesting a UDE Environment
2. Installing D365 Apps
3. Setting Up Your Dev Box
4. Power Platform Tools in Visual Studio
5. Connecting to Dataverse
6. Git Configuration & Model Setup
7. Deploying Models to UDE
8. Database Operations & Debugging
9. Troubleshooting Common Issues

1. Requesting a UDE Environment

Everything starts in the Builder Hub portal. Navigate to the Governance Hub approval request page and click Environment Request. Fill in your display name and environment details across the multi-step form, then submit.

⏳ Plan ahead: After submission, the request is sent to your immediate manager for approval. Don't request the environment the day you need it — factor in approval time.

Once approved, the environment is automatically provisioned and will appear in the Power Platform Admin Center under Environments. From that point, you manage it just like any other Power Platform environment.

How to Use .gitignore in D365 F&O (Complete Guide for Developers)

If your repository is cluttered with unnecessary files, slow check-ins, and confusing changes, your .gitignore likely needs attention—especially in enterprise ERP development. In Dynamics 365 Finance and Operations, managing source control properly isn’t optional; it directly impacts build reliability, collaboration, and deployment efficiency.

This guide walks you through how to use .gitignore effectively in D365 F&O projects—and includes a ready-to-use sample you can download and apply immediately.

---

🚀 Why .gitignore matters in D365 F&O

Development in D365 F&O using Visual Studio produces a large number of temporary, generated, and environment-specific files.

If these files are committed into Git:

• Your repository size grows unnecessarily
• Developers face constant merge conflicts
• Builds become inconsistent across environments

.gitignore ensures only relevant, reproducible source files are tracked.

---

📂 What should NOT go into source control

1) Build outputs & binaries

These are generated during compilation and should never be committed:

**/bin/
**/obj/
**/*.dll
**/*.pdb

---

2) Visual Studio user-specific files

These vary by developer machine:

*.suo
*.user
*.userosscache
*.sln.docstates
.vs/

---

3) D365 F&O generated artifacts

System-generated metadata and temp files:

**/xppMetadata/Temp/
**/xppMetadata/Generated/
**/xppSource/Temp/

D365 Maintenance mode

This blog post provides information about maintenance mode in Finance and Operations. When maintenance mode is turned on, it provides a safe way for system administrators to make system changes that might affect system functionality. For example, configuration keys can be enabled or disabled. While maintenance mode is on, only system administrators and users who have the Maintenance mode user role can sign in to the system. By default, maintenance mode is turned off. 

Turn maintenance mode on and off on sandbox and production environments through Lifecycle Services

You can now turn maintenance mode on and off directly through Lifecycle Services (LCS) on your sandbox and production environments. Refer to the following steps to do this:

1. Go to the environment details page and on the Maintain menu, click Enable Maintenance Mode.
2. In the slider, set Turn maintenance mode on for the environment and select Confirm.
3. A servicing operation will begin and your system will go into maintenance mode.
4. On completion, the environment state will be In Maintenance. At this point, only the system administrator will have access to the environment.
5. After you are done making system-wide changes, you can turn off maintenance mode by clicking Disable Maintenance Mode under the Maintain menu.
6. This will start a servicing operation that takes your environment out of maintenance mode. You can see the progress of the operation in the environment details page.
7. After this is complete, your environment goes back to the Deployed state. Now all users can sign in to the environment.
8. You can check the environment history page to see when the maintenance mode was turned on or turned off. To get to the environment history page, select History and Environment changes on the environment details page.

Turning maintenance mode on and off for your sandbox and production environment is very similar to a servicing operation. If turning maintenance mode on or off fails, you will see options such as Resume, Rollback, and Abort. You also have the option to download the logs to troubleshoot why the operation failed.

Processing an Excel file in batch D365 FO

When talking about extending the SysOperationFramework parts, you probably already know that we will have the well-known structure of classes in order to design our batch process: the controller class, responsible for running the operation and modifying the contract, the contract class, responsible for passing parameters and values from the user to the logic that processes operation, the service class, responsible for providing the logic which is being executed (in our example, iterating through the Excel file, getting the purchase orders IDs and posting a product receipt for them), and the User Interface builder (UIBuilder) class, responsible for the actual looks and event handlers of the dialog that is presented to the user.

Let’s have a look at what our code changes will look like in the D365 user interface, before scheduling the actual batch job:

Analyzing this dialog, we will see that it looks like every other batch dialog, with only one parameter: a file.

Below, we will see the code which is taking care of the design and functionality for this batch process.

The controller class

/// <summary> /// Controller class for creating & posting PO packing slips /// </summary> class AXMPostPOReceiptsController extends SysOperationServiceController { /// <summary> /// Creates new instance of <c>AXMPostPOReceiptsController</c> /// </summary> public void new() { super(); this.parmClassName(classStr(AXMPostPOReceiptsService)); this.parmMethodName(methodStr(AXMPostPOReceiptsService, processOperation)); this.parmDialogCaption("Create packing slips"); }

    /// <summary>

/// Sets the caption of the job /// </summary> /// <returns>Caption of the job</returns> public ClassDescription caption() { return "Create packing slips"; }

    /// <summary> /// Method which is run while calling the corresponding menu item /// </summary> /// <param name = "args">Arguments passed from the menu item</param> public static void main(Args args) { AXMPostPOReceiptsController controller; controller = new AXMPostPOReceiptsController(); controller.startOperation(); } }

Tier1 database restore D365 F&O

The process of database import is needed when a new developer starts to work on a project and requires configured data for performing testing scenarios. In our projects we use preconfigured user data to improve our development and testing performance. We store a Tier 1 environment database backup (.bak) and use it when a new developer comes to work with a project.

As Microsoft releases platform updates, we need, somehow, to use old backups from environments of older versions, for data import on Tier 1 developer environments.

This instruction can be used to import and work with databases for different projects.

Import database

To import database a developer needs to have the database backup.

Important! For this operation you need to use Tier 1 database backup (.bak).

Get database backup

You need to have it to the SQLBACKUP(.bak file)  on Tier 1 DEV machine.

Create new database

Create new database in your DEV box and name it as AxDB_New

Restore the backup 

Restore the database backup in newly created databased (AxDB_New).

Importing data through Data entities using X++

This post shows code to import data in D365 F&O through Data entities using X++ code. One of the best part of this approach is that no more complex joins and relevant tables need to be populate, it just utilizes the power of Data entity and get the data in the system.

To begin with, we will first need to define a data project in Data management workspace as shown in the pictures below.

Step1:

Define a function that will find and return DMFDefinitionGroup.

    private DMFDefinitionGroup findDMFDefinitionGroup(Filename   _fileName)

    {

        DMFDefinitionGroup definitionGroup;

        

        if (strStartsWith(_fileName, 'WorkerEnrolledBenefitDetail'))

        {

            select firstonly definitionGroup

                where definitionGroup.DefinitionGroupName == 'Pledge-GLImport'; //DMF import data project

        }        

        return definitionGroup;

    }

Reading files from Azure Blob container

This post shows X++ code used to list and read all files in Azure Blob container. Please refer to the following code.

    /// <summary>
    /// Start reading files from Blob
    /// </summary>
    public void startReadingFiles()
    {
        CloudBlobClient                 cloudBlobClient;
        CloudBlobContainer              cloudBlobInputContainer;
        CloudStorageAccount             cloudStorageAccount;
        CloudBlockBlob                  iterationBlob, cloudBlockReadBlob;
        Filename                        file, fileName;
        System.Collections.IEnumerable  list;
        System.Collections.IEnumerator  listEnumerator;
        if (!payrollParameters.CUSBlobAccount || !payrollParameters.CUSBlobKey || !payrollParameters.CUSBlobgContainerInput)
        {
            throw error("Missing Parameters");
        }
        var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(payrollParameters.CUSBlobAccount, payrollParameters.CUSBlobKey);
        try
        {
            cloudStorageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);
        
            if (cloudStorageAccount)
            {
                cloudBlobClient = cloudStorageAccount.CreateCloudBlobClient();
                cloudBlobInputContainer = cloudBlobClient.GetContainerReference(payrollParameters.CUSBlobgContainerInput);
                
                list = new System.Collections.Generic.List<str>();
                list = cloudBlobInputContainer.ListBlobs(null, false, 0, null, null);
                listEnumerator = list.getEnumerator();
                while  (listEnumerator.moveNext())
                {
                    iterationBlob = listEnumerator.get_Current();
                    if (iterationBlob.Exists(null, null))
                    {
                        file = iterationBlob.StorageUri.PrimaryUri.AbsoluteUri;
                        fileName = iterationBlob.Name;
                        cloudBlockReadBlob = cloudBlobInputContainer.GetBlockBlobReference(fileName);
                        System.IO.Strean readStream = cloudBlockReadBlob.OpenRead(null, null, null);
                    }
                }
                info("Read from Blob Successful");
            }
        }
        catch
        {
            error("Unable to connect to the Blob");
        }
    }

Sample Positive Pay XSLT

This post contains sample XSLT's for both Payroll and Bank positive pay. 

Bank Positive Pay XSLT

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
exclude-result-prefixes="msxsl xslthelper" 
xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xslthelper="http://schemas.microsoft.com/BizTalk/2003/xslthelper">
  <xsl:output method="text" omit-xml-declaration="yes" version="1.0" encoding="utf-8"/>
  <xsl:template match="/">
    <Document>
      <xsl:for-each select="Document/BANKPOSITIVEPAYEXPORTENTITY">
        <!--Cheque Detail begin-->
<xsl:variable name="terminator">"</xsl:variable>
<xsl:choose>
          <xsl:when test='CHEQUESTATUS/text()=normalize-space("Void") or CHEQUESTATUS/text()=normalize-space("Rejected") or CHEQUESTATUS/text()=normalize-space("Cancelled")'>
            <xsl:value-of select="'C'"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:value-of select="'I'"/>
          </xsl:otherwise>
        </xsl:choose>
<xsl:value-of select="''" />
<xsl:value-of select="substring(concat(ACCOUNTNUM/text(), '          '), 0, 11)"/>
<xsl:value-of select="''" />
<xsl:value-of select="format-number(CHEQUENUM, '0000000000')"/>
<xsl:value-of select="''" />
<xsl:value-of select="translate(format-number(AMOUNTCUR, '00000000.00'), '.', '')"/>
<xsl:value-of select="''" />
<xsl:value-of select="msxsl:format-date(TRANSDATE/text(), 'yyMMdd')"/>
<xsl:value-of select="''" />
<xsl:value-of select="BANKNEGINSTRECIPIENTNAME/text()"/>  
<xsl:text>
</xsl:text>
      </xsl:for-each>
    </Document>
  </xsl:template>
</xsl:stylesheet>

Payroll Positive Pay XSLT

<xsl:stylesheet version="1.0" 

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

xmlns:msxsl="urn:schemas-microsoft-com:xslt" 

exclude-result-prefixes="msxsl xslthelper" 

xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02" 

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

xmlns:xslthelper="http://schemas.microsoft.com/BizTalk/2003/xslthelper">

  <xsl:output method="text" omit-xml-declaration="yes" version="1.0" encoding="utf-8"/>

  <xsl:template match="/">

    <Document>

      <xsl:for-each select="Document/PAYROLLPOSITIVEPAYEXPORTENTITY">

        <!--Cheque Detail begin-->

<xsl:variable name="terminator">"</xsl:variable>

<xsl:value-of select="substring(concat(ACCOUNTNUM/text(), '          '), 0, 11)"/>

<xsl:value-of select="''" />

<xsl:value-of select="format-number(CHEQUENUM, '0000000000')"/>

<xsl:value-of select="''" />

<xsl:value-of select="msxsl:format-date(TRANSDATE/text(), 'MMddyyyy')"/>

<xsl:value-of select="''" />

<xsl:value-of select="translate(format-number(AMOUNTCUR, '0000000000'), '.', '')"/>

<xsl:value-of select="''" />

<xsl:value-of select="substring(BANKNEGINSTRECIPIENTNAME/text(), 0, 30)"/>  

<xsl:value-of select="''" />

<xsl:choose>

          <xsl:when test='CHEQUESTATUS/text()=normalize-space("Void")'>

            <xsl:value-of select="'V'"/>

          </xsl:when>

          <xsl:otherwise>

            <xsl:value-of select="''"/>

          </xsl:otherwise>

        </xsl:choose>

<xsl:choose>

          <xsl:when test='CHEQUESTATUS/text()=normalize-space("Void")'>

            <xsl:value-of select="msxsl:format-date(VOIDEDDATE/text(), 'MMddyyyy')"/>

          </xsl:when>

          <xsl:otherwise>

            <xsl:value-of select="''"/>

          </xsl:otherwise>

        </xsl:choose>

<xsl:text>

</xsl:text>

      </xsl:for-each>

    </Document>

  </xsl:template>

</xsl:stylesheet>

Upload file in Azure Blob using X++

This post shows how to write a code in X++ to upload file in Azure Blob. Please refer to the following X++ method to understand how it works.

    /// <summary>

/// Uploads file to Azure Blob /// </summary> /// <param name = "_csvFileContent">CSV file contents</param> private void uploadToBlob(str _csvFileContent) { CloudBlobClient cloudBlobClient; CloudBlobContainer cloudBlobContainer; CloudStorageAccount cloudStorageAccount; if (!payrollParameters.CUSBlobAccount || !payrollParameters.CUSBlobKey || !payrollParameters.CUSBlobContainer) { throw error("@CMCTS:DonationReportBlobMissingParam"); } System.Byte[] reportBytes = new System.Byte[0](); System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding(); reportBytes = enc.GetBytes(_csvFileContent); System.IO.MemoryStream memoryStream = new System.IO.MemoryStream(reportBytes); var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(payrollParameters.CUSBlobAccount, payrollParameters.CUSBlobKey); try { cloudStorageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true); if (cloudStorageAccount) { cloudBlobClient = cloudStorageAccount.CreateCloudBlobClient(); cloudBlobContainer = cloudBlobClient.GetContainerReference(payrollParameters.CUSBlobContainer); CloudBlockBlob CloudBlockBlob = cloudBlobContainer.GetBlockBlobReference(exportFileName); CloudBlockBlob.UploadFromStream(memoryStream, null, null, null); Info("@CMCTS:UploadToBlobSuccessful"); } } catch { error("@CMCTS:DonationReportAzureBlobConnectError"); error("@CMCTS:UploadToBlobUnSuccessful"); } }

Authorization token decode

I came across a very useful website for decoding authorization token while connecting D365 F&O using .net applications, Postman or any other tool. 

The site is called https://jwt.io/ where you can put authorization token value to decode it.

This site is really helpful in verifying audience value, incorrect audience value can lead to 401 (Unauthorized) error.

Reference Group Control lookup in D365

This post shows how to create lookup for ReferenceGroupControl using extensions.

All we need to do is to register event handler for lookup function and make a lookup little bit differently as shown below.

After handler is registered use the following code.

[FormControlEventHandler(formControlStr(MyWorker, MyWorker_HcmWorkerRecId), FormControlEventType::Lookup)]
public static void MyWorker_HcmWorkerRecId_OnLookup(FormControl sender, FormControlEventArgs e)
{
    MyClass::createLookup(sender);

    FormControlCancelableSuperEventArgs cancelableArgs = e as FormControlCancelableSuperEventArgs;
    cancelableArgs.CancelSuperCall();
}

private static void createLookup(FormReferenceGroupControl _ctrl)
{
    SysReferenceTableLookup sysTableLookup = SysReferenceTableLookup::newParameters(tablenum(HcmWorker), _ctrl);
    Query                   query = new Query();

    sysTableLookup.addLookupfield(fieldNum(HcmWorker, PersonnelNumber));
   
    query.addDataSource(tablenum(HcmWorker));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

Changin NewRecordAction through extensions

In this post we will discuss how to change NewRecordAction property using extensions in D365 for Finance and Operations.

As you know NewRecordAction property is not enabled when being used in extensions as shown in the picture below.

Resolution: 

This can be achieved by capturing onInitialized form handler event as shown below.

How to get D365 environment URL from LCS

This post shows how to get D365 for Finance and Operations environment URL from LCS.

As shown in the picture below, logon to environment, click Login and select Log on to environment.

How to connect UAT database using SQL Server Management Studio

In this post I am will demonstrate how to connect to UAT database (Azure) in SQL server management studio unlike DEV environment where database sits on same box.

Following are the steps involved in connecting SQL server management studio to UAT database:

Step 1:

Go to your environment page in LCS and scroll down to database accounts sections under Manage Environment tab.

Step 2:

Open SQL Server Management Studio in UAT environment and put the highlighted SQLServer\Databasename in Server name box with addition to ".database.windows.net" as shown below

Step 3:

Set the Authentication mode to SQL Server Authentication, get the password from environment page under database accounts section and hit Connect.

You are now connected to UAT database.

Inventory transaction statuses

The statuses of inventory transactions represent different process steps in a normal demand and supply flow. The status impacts the different on-hand balances that are kept in the inventSum table.

• StatusIssue statuses represent demand and can be thought of as subtractions from the inventory.
• StatusReceipt statuses represent supply and can be thought of as additions to the inventory.

List below describes the StatusIssue and StatusReceipt statuses are used in Microsoft Dynamics 365 for Finance and Operations and AX 2012. 

Status Issue

• None
• Sold
• Deducted
• Picked
• ReservPhysical
• ReservOrdered
• OnOrder
• QuotationIssue

Status Receipt

• None
• Purchased
• Received
• Regestired
• Arrived
• Ordered
• QuotationReceipt

Let's review all of them in details.

Changing Splash Screen in D365 Finance and Operations

In this post I am going to show changing splash screen in Dynamics 365 Finance and Operations. In order to change that you need to go under webroot folder found in AOSService folder, in this folder you will find html file with name "App".

In App.html file you will find the following code:

Stylesheet of Splash Screen

Html code of Splash Screen