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.