Cleaning Up Duplicate Records in SQL Server: A Practical Walkthrough

🧹 How to Identify and Remove Duplicate Records in SQL Server

Duplicate records are a common headache in databases. They mess up reporting, inflate numbers, and introduce inconsistencies that can quietly break things. Whether you're cleaning legacy data or preventing future problems, knowing how to find and eliminate duplicates is an essential SQL skill.

Here’s a practical approach using a sample table we'll call xxyyzz. 

Step 1: Explore the Table

Start by reviewing what’s in the table:

SELECT * FROM xxyyzz;

This gives you the lay of the land. Before doing anything destructive, it's crucial to understand which columns matter when determining if two rows are considered duplicates.

Step 2: Detect Duplicates

Let’s say duplicates are defined by a combination of columns — for example, everything except a unique ID or timestamp. You can group by those columns and count how many times each combination appears.

SELECT 

    column1, column2, column3, ..., columnN,

    COUNT(*) AS DuplicateCount

FROM xxyyzz

GROUP BY 

    column1, column2, column3, ..., columnN

HAVING COUNT(*) > 1;

Replace column1 through columnN with the fields that define a unique row in your context.

Step 3: Remove Duplicates Using a CTE

Now comes the cleanup. You want to keep one row from each group and delete the rest. This is where a Common Table Expression (CTE) with ROW_NUMBER() comes in.

WITH DuplicatesCTE AS (

    SELECT *,

        ROW_NUMBER() OVER (

            PARTITION BY column1, column2, column3, ..., columnN

            ORDER BY ID -- or CreatedDate, or another tie-breaker

        ) AS rn

    FROM xxyyzz

)

DELETE FROM DuplicatesCTE WHERE rn > 1;

What this does:

• The ROW_NUMBER() function assigns a row number to each record in each duplicate group.

• Rows are grouped by the columns that define uniqueness.

• The row with rn = 1 (usually the oldest or lowest ID) is kept.

• Everything else (rn > 1) is deleted.

⚠️ Always back up your data or test in a staging environment before running deletes.

✅ Optional: Prevent Future Duplicates

If this table should never have duplicates in the first place, consider enforcing a constraint with a unique index:

CREATE UNIQUE INDEX UX_xxyyzz_UniqueRows

ON xxyyzz (column1, column2, column3, ..., columnN);

This will block any future insertions that would create another duplicate.

Final Thoughts

Cleaning duplicates is routine, but it pays off every time. It enhances data accuracy, streamlines queries, and optimises performance. The combo of GROUP BY, ROW_NUMBER(), and a targeted approach DELETE gives you a clean, reliable fix.

Let this be part of your standard SQL toolkit — and if you need help customising it for a specific dataset or situation, just ask. 

 

How to Convert Between List and Container in X++ for JSON Serialization

How to Convert Between List and Container in X++ for JSON Serialization

In Dynamics 365 Finance and Operations (D365FO), working with data serialization, especially when dealing with lists or arrays, can be a bit tricky. The native List class and container type don't work in exactly the same way when it comes to serialization in JSON. In this blog post, we'll explore how to convert between a List of strings and a container, a fundamental task for serializing collections of data correctly in D365FO.

What Are Lists and Containers?

• List: A dynamic collection of elements. In X++, the List class is used to store collections of objects, such as strings, integers, or other data types.

• Container: A generic type in X++ that can store multiple data types. It's often used to hold values of different types (like integers, strings, or even other containers). For serialization, container is the preferred type when dealing with collections in Dynamics 365.

When you need to serialize a collection of strings (such as tags, categories, etc.) as part of a DataContract class for an API or service, you might encounter issues since X++ doesn't natively allow you to directly serialize a List of strings into JSON. Instead, you'll need to convert the List to a container, serialize it, and later convert it back to a List.

Why Do We Need This Conversion?

When using AIF (Application Integration Framework) or Data Contracts in D365FO, the container type is automatically supported for JSON serialization. However, List is not directly serializable. As a result, we need to implement conversion methods to handle this transformation when dealing with collections like strings or integers.

Here, we'll walk through how to create two utility methods that help convert between a List and a container in X++.

The Conversion Methods

1. List to Container Conversion

This method converts a List of strings into a container. The key here is iterating over each item in the List and adding it to a new container:

x++
public static container listToContainer(List listOfStrings)
{
    container result;
    ListEnumerator enumerator = listOfStrings.getEnumerator();

    while (enumerator.moveNext())
    {
        result += enumerator.current();
    }

    return result;
}

Explanation:

• We initialize an empty container called result.

• The ListEnumerator allows us to loop through the List one item at a time.

• We use the += operator to add each item from the List into the container.

• Finally, the container is returned, now holding the same items as the original List.

2. Container to List Conversion

The reverse operation converts a container back into a List of strings. Here's the implementation:

x++
public static List containerToList(container con)
{
    List result = new List(Types::String);
    int i;

    for (i = 1; i <= conLen(con); i++)
    {
        result.addEnd(conPeek(con, i));
    }

    return result;
}

Explanation:

• We initialize an empty List of type string called result.

• The conLen function gets the number of elements in the container.

• We loop through the container, extracting each element using conPeek() and adding it to the List using addEnd().

• Finally, we return the populated List.

---

Using the Conversion Methods in Your Code

Let's see how you can use these helper methods in a typical use case, such as managing shipment tags in a Data Contract class.

Updated ShipmentTags Method Example

Here’s how you would use the conversion methods in a ShipmentTags property in your Data Contract:

x++
[DataMemberAttribute(#ShipmentTags), AifCollectionTypeAttribute(#_ShipmentTags, Types::String)]
public List ShipmentTags(List _ShipmentTags = containerToList(fpShipmentTags.parmValue()))
{
    if (!prmisDefault(_ShipmentTags))
    {
        fpShipmentTags.parmValue(listToContainer(_ShipmentTags));
    }

    return _ShipmentTags;
}

Explanation:

• Serialization: When the method is called, if a List of strings is passed, it gets converted to a container for serialization. This is achieved through the listToContainer() method.

• Deserialization: When deserializing data, the container is converted back to a List of strings using the containerToList() method.

Now, you can easily assign values to the ShipmentTags property as follows:

x++
// Create a list of strings
List shipmentTagsList = new List(Types::String);
shipmentTagsList.addEnd("Fragile");
shipmentTagsList.addEnd("Express");
shipmentTagsList.addEnd("HandleWithCare");

// Create an instance of your contract class
CarIntApiTrkCreateContract contract = CarIntApiTrkCreateContract::construct();

// Assign the list to the ShipmentTags property
contract.ShipmentTags(shipmentTagsList);

This will serialize into the following JSON:

json
"shipmentTags": ["Fragile", "Express", "HandleWithCare"]

Conclusion

By implementing the ListToContainer and ContainerToList conversion methods, you can easily manage collections of primitive types (like strings) for JSON serialization in Dynamics 365 Finance and Operations. These utility methods allow you to seamlessly work with List objects in your code while ensuring compatibility with D365FO's serialization framework.

If you're building a custom service or API that involves sending or receiving lists of data, these conversion methods will be crucial for ensuring that data is properly serialized and deserialized. They streamline your development process, avoiding the need for manual conversion every time you work with lists in Data Contracts.

 

Secure API Integration in D365FO Using OAuth2 and Azure Key Vault (X++)

Integrating with external APIs securely is a common requirement in Microsoft Dynamics 365 Finance and Operations (D365FO) projects. In this post, we will walk through a complete, secure, and reusable approach for integrating a third-party API using OAuth2 and Azure Key Vault to retrieve secrets like the client secret.

Use Case

You want to:

• Authenticate using OAuth2 client credentials flow.

• Securely retrieve the client secret from Azure Key Vault.

• Send a JSON-based POST request to an external API.

• Build this in a reusable way using standard .NET classes in X++.

Solution Overview

We'll create a reusable class NarviAPIClient with two core methods:

1. getOAuthTokenFromKeyVault() - Retrieves an access token securely.

2. sendPostRequest() - Sends a POST request with Authorization header and JSON payload.

Step-by-Step Implementation

Step 1: Create NarviAPIClient Class in AOT

class NarviAPIClient

{

    public static str getOAuthTokenFromKeyVault(str clientId, str tenantId, str resource, str secretName)

    {

        str tokenEndpoint, clientSecret, formData, responseText, accessToken;

        System.Net.HttpWebRequest tokenRequest;

        System.Net.HttpWebResponse tokenResponse;

        System.IO.Stream responseStream;

        System.IO.StreamReader streamReader;

        System.Text.UTF8Encoding encoding;

        System.Byte[] bytes;

        Map tokenMap;

        KeyVaultParameters keyVaultParameters = KeyVaultParameters::construct();

        clientSecret = KeyVaultCertificateHelper::getSecretValueFromKeyVault(secretName, keyVaultParameters);

        tokenEndpoint = strFmt("https://login.microsoftonline.com/%1/oauth2/token", tenantId);

        formData = strFmt("grant_type=client_credentials&client_id=%1&client_secret=%2&resource=%3",

                          clientId, clientSecret, resource);

        try

        {

            tokenRequest = System.Net.WebRequest::Create(tokenEndpoint) as System.Net.HttpWebRequest;

            tokenRequest.set_Method("POST");

            tokenRequest.set_ContentType("application/x-www-form-urlencoded");

            encoding = new System.Text.UTF8Encoding();

            bytes = encoding.GetBytes(formData);

            tokenRequest.get_RequestStream().Write(bytes, 0, bytes.Length);

            tokenResponse = tokenRequest.GetResponse();

            responseStream = tokenResponse.GetResponseStream();

            streamReader = new System.IO.StreamReader(responseStream);

            responseText = streamReader.ReadToEnd();

            tokenMap = RetailCommonWebAPI::parseJSON(responseText);

            accessToken = tokenMap.lookup("access_token");

            return accessToken;

        }

        catch (Exception::CLRError)

        {

            error(strFmt("OAuth token error: %1", AifUtil::getClrErrorMessage()));

            return "";

        }

    }

    public static str sendPostRequest(str url, str jsonBody, str accessToken)

    {

        System.Net.HttpWebRequest httpRequest;

        System.Net.HttpWebResponse httpResponse;

        System.IO.Stream requestStream, responseStream;

        System.IO.StreamReader reader;

        System.Text.UTF8Encoding encoding;

        System.Byte[] bytes;

        str responseText;

        try

        {

            httpRequest = System.Net.WebRequest::Create(url) as System.Net.HttpWebRequest;

            httpRequest.set_Method("POST");

            httpRequest.set_ContentType("application/json");

            httpRequest.Headers.Add("Authorization", strFmt("Bearer %1", accessToken));

            encoding = new System.Text.UTF8Encoding();

            bytes = encoding.GetBytes(jsonBody);

            requestStream = httpRequest.GetRequestStream();

            requestStream.Write(bytes, 0, bytes.Length);

            httpResponse = httpRequest.GetResponse();

            responseStream = httpResponse.GetResponseStream();

            reader = new System.IO.StreamReader(responseStream);

            responseText = reader.ReadToEnd();

            return responseText;

        }

        catch (Exception::CLRError)

        {

            return strFmt("POST error: %1", AifUtil::getClrErrorMessage());

        }

    }

}

Step 2: Use the Class from a Job or Service

static void CallNarviAPIClient(Args _args)

{

    str tenantId     = "<your-tenant-id>";

    str clientId     = "<your-client-id>";

    str resource     = "<your-api-resource-uri>";

    str secretName   = "your-client-secret-keyvault-name";

    str apiUrl       = "https://your-api-endpoint.com/api/post";

    str jsonPayload, accessToken, response;

    System.IO.StringWriter writer = new System.IO.StringWriter();

    Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(writer);

    jsonWriter.WriteStartObject();

    jsonWriter.WritePropertyName("ProjId");

    jsonWriter.WriteValue("000123");

    jsonWriter.WritePropertyName("TransDate");

    jsonWriter.WriteValue("2025-05-13");

    jsonWriter.WriteEndObject();

    jsonPayload = writer.ToString();

    accessToken = NarviAPIClient::getOAuthTokenFromKeyVault(clientId, tenantId, resource, secretName);

    if (!accessToken)

    {

        error("Token not retrieved.");

        return;

    }

    response = NarviAPIClient::sendPostRequest(apiUrl, jsonPayload, accessToken);

    info(strFmt("API Response: %1", response));

}

Benefits of This Approach

• Secure: Client secrets aren't hardcoded.

• Reusable: Centralized logic for token generation and POST request.

• Modular: Easily extendable for GET requests, token caching, retries, etc.

• Production Ready: Compatible with batch and service classes.

Prerequisites

• Azure Key Vault configured in LCS.

• The app must have permissions to read secrets.

• Secrets must be stored in Key Vault.

Enable Power Platform Integration: Set up D365FO integration with Dataverse

 

Set up D365FO integration with Dataverse

 This document shows how to set up D365FO integration with Dataverse.

Create a security role

1. Sign in to the Power Platform admin centre and select an environment.

2. Select Settings > Users + permissions > Security roles.

3. Select + New role.

4. Enter a role name.
5. Select a business unit.
6. To allow team members to inherit the privileges of this role when it's assigned to a team, accept the default Member's privilege inheritance setting, which is Direct User (Basic) access level and Team privileges.
7. Select Save. The properties of the new role are displayed.

8. Enter your table name in the Search input field to find your app's table.

9. Select your table and set the Permission settings. Then select the Save button.
10. Assigning new role to user
1. Select user and click 3 dots.

2.      Select Manage security roles.

3.      Select newly created role and save.