How to Run SQL Command Using X++ Code

How to run SQL command from X++

Scenario:

In certain scenarios, we may need to execute SQL commands in sandbox environments where direct database access is restricted. For example, when troubleshooting data inconsistencies, performing bulk updates. We cannot run SQL queries directly in a sandbox environment.

To overcome this limitation, we can execute SQL commands programmatically using X++ by leveraging the SysDatabase::executeQuery or Connection class. This approach allows us to run necessary SQL operations while adhering to security and system constraints.

High level resolution steps

We can follow the steps below to run SQL command from x++.

Add the SQL command in resource.

Call the resource in code.

Add the code to run the SQL command

Run the class

Detailed resolution steps

Step 1: Create a resource and select the SQL file which you want to execute.

Navigate to Label and Resources select resource, Give the name and click on add.

Now it will open a window to select the SQL File

Step 2: Create a runnable class or a batch class and add the below code

internal final class SCCSqlTesTT
{
    public static void main(Args _args)
    {
        str sql;
        Connection conn;
        SqlStatementExecutePermission permission;
        ResultSet resultSet;
        Statement statement;

        // Read the SQL script from an embedded resource file and store it in the 'sql' variable.
        using (System.IO.Stream resourceStream =
            Microsoft.Dynamics.Ax.Xpp.MetadataSupport::GetResourceContentStream(resourceStr(SCCSqlTest))) // Add your resource file here.
        {
            sql = new System.IO.StreamReader(resourceStream, System.Text.Encoding::Default).ReadToEnd();
        }

        // Assigns execute permission for the SQL statement.
        permission = new SqlStatementExecutePermission(sql);

        // Establish a new database connection.
        conn = new Connection();

        // Reassigning execute permission (redundant, as it's already assigned above).
        permission = new SqlStatementExecutePermission(sql);

        // Asserts the permission to execute the SQL statement.
        permission.assert();

        // Execute the SQL statement, typically an INSERT, UPDATE, or DELETE command.
        conn.createStatement().executeUpdate(sql);

        // Create a SQL statement object that can be used for execution (though not used here).
        statement = conn.createStatement();

        // Reverts the previously asserted permission to execute SQL, restoring security settings.
        CodeAccessPermission::revertAssert();
    }
}

Add your resource file here

using(System.IO.Stream resourceStream = Microsoft.Dynamics.Ax.Xpp.MetadataSupport::GetResourceContentStream(resourceStr(SCCSqlTest)))
{
sql = new System.IO.StreamReader(resourceStream, System.Text.Encoding::Default).ReadToEnd();
}

Reference code Snapshot.

internal final class SCCSqlTesTT
{
    /// 
    /// Class entry point. The system will call this method when a designated menu
    /// is selected or when execution starts and this class is set as the startup class.
    /// 
    /// The specified arguments.
    public static void main(Args _args)
    {
        str sql;
        Connection conn;
        SqlStatementExecutePermission permission;
        ResultSet resultSet;
        Statement statement;

        using (System.IO.Stream resourceStream = Microsoft.Dynamics.Ax.Xpp.MetadataSupport::GetResourceContentStream(resourceStr(SCCSqlTest)))
        {
            sql = new System.IO.StreamReader(resourceStream, System.Text.Encoding::Default).ReadToEnd();
        }

        permission = new SqlStatementExecutePermission(sql);
        conn = new Connection();

        permission = new SqlStatementExecutePermission(sql);
        permission.assert();

        conn.createStatement().executeUpdate(sql);
        statement = conn.createStatement();

        CodeAccessPermission::revertAssert();
    }
}

Now, you can execute the class, and the SQL command will run successfully.

Output

This is the SQL command that I have in the SQL file.