Skip to main content

How to create new (missing) columns for entities in your Data Export Service Azure SQL database

Post a Comment

How to create new (missing) columns for entities in your Data Export Service Azure SQL database

Usually the Data Export Service (DES) automatically picks up changes on entities and creates new fields/columns in the Azure SQL database that is connected to your DES profile.

Our experience was that this automatism is not always reliable thus we had to come up with a workaround.

Microsoft Support suggested us to create missing columns on the export database ourselves manually, which I did. As there is currently no other official documentation on this topic I thought I should share this article with the rest of our awesome community.

1. Completely recreate the entity table

The 1st approach to solve this issue would be to go inside your DES profile and deselect the target entity from the list of entities to sync and save the changes. Next delete the target entities tables and user defined table types with the script provided here:

https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database#how-to-delete-data-export-profile-tables-and-stored-procedures-for-a-specific-entity

Once deleted you can go back in your DES profile and re-enable the target entity. Once saved in the next sync run the entity table and table type will get recreated.

The Big BUT for this approach...

From my prospective this approach is only acceptable for DEV or TEST environments with little data, where it does not hurt to loose that data and resync everything again.

For production environments this way is a big NO GO. You could have further integrations that are dependent on that data, e.g. reports, ERP integrations or interfaces in 3rd party systems.

For this reason there needs to be another way to solve this problem...

2. Only amend the existing entity table and recreation of the related user defined table type

ALTER TABLE statement for the entity table that is missing the new columns

ALTER TABLE ov.account
ADD [ak_detergentsmanualtoolspy] [decimal](38, 4) NULL,
[ak_detergentsmanualtoolspy_base] [decimal](38, 4) NULL,
[ak_detergentsmanualtoolsytd] [decimal](38, 4) NULL,
[ak_detergentsmanualtoolsytd_base] [decimal](38, 4) NULL,
[ak_aftermarketservicesytd] [decimal](38, 4) NULL,
[ak_aftermarketservicesytd_base] [decimal](38, 4) NULL,
[ak_aftermarketservicespy] [decimal](38, 4) NULL,
[ak_aftermarketservicespy_base] [decimal](38, 4) NULL

Recreate User-Defined Table Type
The DES requires to have the same columns definitions for both the UDT table type and the entity table, otherwise it will throw an error while syncing.
As the UDT-Table types cannot be altered we need to recreate them.
On your data export database in the left navigation tree, open the Programmability folder, navigate to Types and search for your target entity type:
Select the type, right-click on it and create a script to create a new UDT type:
Keep the new script query open and add the missing lines to the bottom of the query: 



CREATE TYPE [ov].[accountType] AS TABLE(
    [Id] [uniqueidentifier] NOT NULL,
    [SinkCreatedOn] [datetime] NULL,
    [SinkModifiedOn] [datetime] NULL,
.
.
.
    [ak_detergentsmanualtoolspy] [decimal](38, 4) NULL,
    [ak_detergentsmanualtoolspy_base] [decimal](38, 4) NULL,
    [ak_detergentsmanualtoolsytd] [decimal](38, 4) NULL,
    [ak_detergentsmanualtoolsytd_base] [decimal](38, 4) NULL,
    [ak_aftermarketservicesytd] [decimal](38, 4) NULL,
    [ak_aftermarketservicesytd_base] [decimal](38, 4) NULL,
    [ak_aftermarketservicespy] [decimal](38, 4) NULL,
    [ak_aftermarketservicespy_base] [decimal](38, 4) NULL
)
GO



Then on the left navigation pane select the old type and select "Delete" from the context menu.
Once deleted you can execute the new UDT type script and recreate the UDT table type.
Now wait until the next sync run and the data for the new columns should get exported to your new columns. 













Labels:






























Comments











Post a Comment



















Popular posts from this blog




























Yet Another Address Autocomplete PCF Control–powered by Bing




























In this blog post I will not go into detail in how to install all the pre-requisites that are required to build and run PCF controls. My goal was to build a new PCF control and get into coding of PCF controls as fast as possible.


Here are a few links to articles that will help you installing the pre-requisites (Microsoft PowerApps CLI) https://docs.microsoft.com/en-us/powerapps/developer/component-framework/get-powerapps-cli

Other good references to get into this topic:
https://toddbaginski.com/blog/how-to-create-a-powerapps-pcf-control/
https://docs.microsoft.com/en-us/powerapps/developer/component-framework/create-custom-controls-using-pcf

I looked through the Guido Preite’s https://pcf.gallery/ which will help you find appropriate use cases / examples for your own needs. It did not take very long to find a simple example to start with: Andrew Butenko's https://pcf.gallery/address-autocomplete/

A few moments later I had the idea to create yet another address autocomplete control b…




















8 comments









Read more



































Regarding SPFieldMultiLineText (Add HTML/URL content to a field) programmatically




























I recently tried so set some HTML content in a SharePoint list column of type SPFieldMultiLineText.


My first approach was this piece of code:

SPFieldMultiLineText field = item.Fields.GetFieldByInternalName("Associated Documents") as SPFieldMultiLineText;

StringBuilder docList = new StringBuilder();
docList.Append("");
foreach (DataRow docRow in addDocs)
{
DataRow[] parent = dr.Table.DataSet.Tables[0].Select("DOK_ID=" + docRow["DOK_MGD_ID"].ToString());
if (parent != null && parent.Length > 0)
{
docList.AppendFormat("{1}", parent[0]["FilePath"].ToString(), parent[0]["Title"].ToString());
}
}
if (docList.Length > 0)
{
// remove trailing tag
docList.Remove(docList.Length-5, 5);
}
docList.Append(" ");
string newValue = docList.ToString();
item[field.Title] = newValue;


What this code does is to get all associated documents to the main document and to add these document uri links to the "Assoc…




















1 comment









Read more





































Set Multi-OptionSet values with a custom workflow activity




























As the relatively new multi-optionset field type arrived with v.9 of Dynamics 365 the need to set values via workflow is quite a common requirement. So I searched within the community to find some ideas on how to solve and create such a workflow activity and so I stumbled upon Demian Raschkovan’s Workflow Tools with can be found on his github repository: https://github.com/demianrasko/Dynamics-365-Workflow-Tools/blob/master/msdyncrmWorkflowTools/msdyncrmWorkflowTools/Class/MapMultiSelectOptionSet.cs
 
It gave me some basic ideas to reach my requirements which are: Should be generic for any type of entity Ability to specify the attribute name of the required multi-optionset for that entity Provide a list of multi-optionset values (comma-separated) Keep existing values (True/Yes => add provided values / False/No => replace all values with the provided values Remove specific value(s) from an existing set of values 
My code sample (without other dependencies): 
 using Microsoft.Xrm.Sdk;
using Micr…




















1 comment









Read more