Skip to main content

Power Automate: SharePoint Copy file action fails on file names with a plus (+) sign

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.

Comments

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

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 docu

CRM 2016: The attribute with AttributeId = 'guid' was not found in the MetadataCache.

During the export of an unmanged solution I always received the following error message from my CRM 2016 SP1 OnPremise development system: The attribute with AttributeId = '023bda49-9dfa-401e-b348-9374a4141186' was not found in the MetadataCache. Then I tried to remember what I did since the last solution version. => Yes, there was a field that I deleted, because the customer does not need it anymore. Because I am working on-premise it was easy to check the database for the specific attribute. First, I searched in the attribute table: SELECT [AttributeId]   FROM [Dev_MSCRM].[MetadataSchema].[Attribute]   where [AttributeId] = '023bda49-9dfa-401e-b348-9374a4141186' => result was nothing I concluded CRM deleted the field in this table but missed to remove the attribute from our customer solution. Next thing was to check the solutioncomponent table: SELECT [ModifiedOn]       ,[CreatedBy]       ,[ObjectId]       ,[IsMetadata]       ,[ModifiedBy