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



































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




















1 comment









Read more



































CRM 2016 SP1: Solution import failed with Solution With Id = 12ac16ec-41d5-1685-a230-0b1c31499250 Does Not Exist 




























Importing Solutions with the upgrade solution (holding solution) option is still not stable with CRM 2016 SP1 On-Premise.   Ronald Lemmon already posted the same issue on his blog:  http://ronaldlemmen.blogspot.de/2015/11/solution-with-id-86ac16ec-41d7-4685.html   I expected it to be fixed with SP1 but it is still happening every now and then.   With this query and the solution id (guid) of the error message you are able to find records in the ProcessTriggerBase table that reference the guid of a failed import from a holding solution that is not existing in the system anymore.    select  * from  ProcessTriggerBase where  solutionid = 'YOUR_SOLUTION_GUID'    Due to this reference to a solution id not existing in the system you will not be able to import a new version of the target solution to be updated.   What I did and this is probably not a supported solution is to update the guid to the actual target solution that is currently deployed in the system.   To find out the id of 




















3 comments









Read more