Skip to main content

XrmToolBox: AutoNumberUpdater - new StateCode filter

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...




















16 comments









Read more





































Power Automate: Avoid Apply to Each for List rows action (DataVerse)




























If you do not want to use the Apply to Each loop after a Dataverse List rows action you can use the following in a Dynamic Content / Expression box to directly use a field's value in e.g. Compose action: first(outputs('List_rows')?['body/value'])?['apx_fullpath'] instead of apx_fullpath you can use whatever field name you need for the entity that's been queried before. 




















Post a Comment









Read more




































How to add your personal PowerBI Dashboard as a system dashboard




























New Approach: Attention: Microsoft added this feature out of the box, so that the manual approach below is not required anymore. More to this can be found here: https://powerapps.microsoft.com/en-us/blog/power-bi-embedded-as-system-dashboard-in-model-driven-apps-preview/ Old Approach: The general guideline on how to create a personal dashboard is described in this article:  https://docs.microsoft.com/en-us/powerapps/user/add-powerbi-dashboards   But what if you want to rollout a global PowerBI dashboard for all users?  With some tweaks you can manage it to make it work…  1. After you have created your personal dashboard you need to extract the formxml of that dashboard with this URL query in a browser tab:  https://org.crm4.dynamics.com/api/data/v9.1/userforms?$select=name,formxml&$filter=type eq 103 and name eq 'Ticket Status'  Replace…  type = 103 => PowerBi Dashboard  name = 'Your PowerBI Dashboard Name'  In the result copy the XML part out of the "formxm...




















Post a Comment









Read more