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.

Legitimate use cases include:

• One-time data migration or correction scripts run under administrator supervision.
• Aggregation queries that are orders of magnitude faster as raw SQL than as X++ set-based operations.
• Querying system or metadata tables that aren't exposed as X++ data entities.
• Building diagnostic or monitoring dashboards for DBAs.

Building the DirectSqlExecutor Class

We'll create a class with three clear responsibilities:

1. Execute a non-query (INSERT, UPDATE, DELETE) and return the affected row count.
2. Handle errors and resource cleanup so callers don't have to.

/// <summary>

/// Utility class for executing direct SQL statements against the

/// D365FO runtime database. Use sparingly — this bypasses standard

/// X++ data access security, eventing, and logging.

/// </summary>

class DirectSqlExecutor

{   

    /// <summary>

    /// Executes a non-query SQL statement (INSERT, UPDATE, DELETE).

    /// </summary>

    /// <param name = "_sqlStatement">The SQL statement to execute.</param>

    /// <returns>The number of rows affected.</returns>

    public static int executeNonQuery(str _sqlStatement)

    {

        Connection  connection;

        Statement   statement;

        int         rowsAffected = 0;

        DirectSqlExecutor::validateStatement(_sqlStatement, false);

        try

        {

            connection = new Connection();

            statement  = connection.createStatement();

            rowsAffected = statement.executeUpdate(_sqlStatement);

            info(strFmt("Direct SQL executed. Rows affected: %1", rowsAffected));

        }

        catch (Exception::Error)

        {

            error(strFmt("Direct SQL execution failed: %1", _sqlStatement));

            throw Exception::Error;

        }

        return rowsAffected;

    }

    /// <summary>

    /// Basic validation to catch common mistakes before they hit the database.

    /// </summary>

    /// <param name = "_sqlStatement">The SQL statement to validate.</param>

    private static void validateStatement(str _sqlStatement)

    {

        str trimmed = strLTrim(strRTrim(_sqlStatement));

        if (strLen(trimmed) == 0)

        {

            throw error("SQL statement cannot be empty.");

        }

        str upper = strUpr(trimmed);

        

        if (strStartsWith(upper, "SELECT"))

        {

            throw error("executeNonQuery does not accept SELECT statements.");

        }

        // Guard against accidental DDL in a class designed for DML only.

        if (strStartsWith(upper, "DROP")

            || strStartsWith(upper, "ALTER")

            || strStartsWith(upper, "CREATE")))

        {

            throw error("DDL statements (DROP, ALTER,  AND CREATE) are not permitted.");

        }

    }

}

Running an Update

int affected = DirectSqlExecutor::executeNonQuery(

    strFmt("UPDATE CUSTTABLE SET NAMEALIAS = 'Updated' WHERE ACCOUNTNUM = '%1' AND DATAAREAID = 'usmf'","US-001"));

info(strFmt("Updated %1 customer record(s).", affected));

More practical approach

class ExecuteCustomSQLScript

{

    public static void main(Args _args)

    {

        Memo queryInput;

        if (ExecuteCustomSQLScript::isSysAdminRole()) 

        {

            Dialog dialog = new Dialog("Enter SQL Query");

            DialogField dialogQueryInput = dialog.addField(

            extendedTypeStr(Memo)); 

            if (dialog.run())

            {

                queryInput = dialogQueryInput.Value();

            }

            SqlStatementExecutePermission permission;

            ttsbegin;

            Connection conn = new Connection();

            permission = new SqlStatementExecutePermission(queryInput);

            permission.assert();

            conn.createStatement().executeUpdate(queryInput);

            CodeAccessPermission::revertAssert();

            ttscommit;

        }

    }

/// <summary>

    /// isSysAdminRole

    /// </summary>

    /// <returns>boolean</returns>

    public static boolean isSysAdminRole()

    {

        SecurityRole        securityRole;

        SecurityUserRole    securityUserRole;

        SysUserId           currentUserId   = curUserId();

    select firstonly securityRole

        where securityRole.Name                 == "@SYS344030"

    exists join securityUserRole

        where securityUserRole.User             == currentUserId

            && securityUserRole.SecurityRole    == securityRole.RecId;

       return securityRole.RecId ? true : false;

    }

}