Getting Workflow Editor and Management Reporter Editor to work in Chromium Edge

When running Dynamics 365 for Finance and Operations there are some legacy software built in. These are packaged as ClickOnce applications and will be downloaded every time you run them. In Internet Explorer and the old version of Microsoft Edge this worked out of the box since these are .Net aware out of the box. When is comes to the all new chromium based Microsoft Edge and Google Chrome this is not the case. In order to get these working we will need a plugin installed in the browser.

For Microsoft (Chromium) Edge
To install in Edge the process is almost the same as for Google Chrome but with an additional steps prior to starting

  • Now go directly to step 3 below

Tip: you can bookmark the URL to the Google Chrome Store to have it around

For Google Chrome
1. Go to the Chrome Web Store by going to Extensions in the Settings menu

2. Click the Chrome Web Store at the bottom of the page

3. Search for ClickOnce and Install ClickOnce for Google Chrome

Issues importing Data Package

In one of our environments we had an issue where we were not able to import a data package from a file. When trying the import it simply did not list the entities included in the package. So we tried the usual; restarting the DIXF service, Restarting the VM…. No luck.

Looking in the Event Viewer we found an error from the Microsoft Dynamics AX DIXF Service Runtime Provider:

Error In SSIS Execution

System.Exception: SYS105313 —> System.InvalidOperationException: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Microsoft.Dynamics.AX.Framework.Tools.DMF.DriverHelper.DMFOdbcDriver.GetSheetNamesFromExcel(String sourceFileURL) — End of inner exception stack trace — at Microsoft.Dynamics.AX.Framework.Tools.DMF.DriverHelper.DMFOdbcDriver.GetSheetNamesFromExcel(String sourceFileURL) at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.Service.ServiceHelper.GetSheetNamesFromExcel(String sourceFile)

I used a bit of Google-Fu and found that this is probably an issue with the Access Database engine. Since all Dynamics 365 FO VMs are deployed as ready-made VMs and we are not responsible for any installation the alternative to fixing this issue is to just redeploying the VM. In this case I tried to fix it. I downloaded this install file from Microsoft and ran a repair.

This solved the issue and re-registered the DLLs needed.

Links:
https://www.mikesdotnetting.com/article/280/solved-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-mach

Changing branches for a new sprint

In the project I am working on right now we are maintaining two release branches. One for sprint release and one for hotfixes. Every time we release a sprint we are re-targeting the the build pipelines to point to the new branches for the next sprint. This article is a short description for where you need to change path (mostly for me to remember):

  1. Log into Azure DevOps
  2. Go to Pipelines – Builds
  3. Select the Pipeline you want to change and click Edit
  4. Go to Get Sources and change the two fields under Server Path

  5. Select the workflow item called Build the solution and change to the correct path in Project

  6. Save the pipeline.

    Note that when you are looking at the pipeline you will not see the correct branch until you have actually run the build successfully once

That is it for today…

Johan

 

Setting up notifications in Azure DevOps

Today one of the test guys in our project asked if it was possible to get a notification when code was pushed to a specific branch in DevOps… specifically the branch that meant that they needed to test something… this is what you do:

  1. Login into Azure Devops and go to User Setting – Notifications
  2. Click New Subscription, choose Code (TFVC) and Code is checked in
    Note: since this is a Dynamics 365 FO project we are using TFVC 
  3. Set the Path (or other criteria) you want to check for and click finish

    That is all…

Johan

Link
https://docs.microsoft.com/en-us/azure/devops/boards/queries/alerts-and-notifications?view=tfs-2017

Copy database from an Azure Sql Environment to a OneBox environment

Here is a checklist to copy a Dynamics 365 for Finance and operations database in from a tier-2 (or above) to a OneBox (Dev, Build or Test).

  1. Start by creating a new database as a copy of the original (since you are going to mess with it) by running this SQL query:
    CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy

    Since this operation runs asynchronously in the background you will have to keep an eye on it before continuing:

    SELECT * FROM sys.dm_database_copies
  2. Prepare the database for export
    --Prepare a database in Azure SQL Database for export to SQL Server.
    --Disable change tracking on tables where it is enabled.
    declare
    @SQL varchar(1000)
    set quoted_identifier off
    declare changeTrackingCursor CURSOR for
    select 'ALTER TABLE ' + t.name + ' DISABLE CHANGE_TRACKING'
    from sys.change_tracking_tables c, sys.tables t
    where t.object_id = c.object_id
    OPEN changeTrackingCursor
    FETCH changeTrackingCursor into @SQL
    WHILE @@Fetch_Status = 0
    BEGIN
    exec(@SQL)
    FETCH changeTrackingCursor into @SQL
    END
    CLOSE changeTrackingCursor
    DEALLOCATE changeTrackingCursor
    
    --Disable change tracking on the database itself.
    ALTER DATABASE
    -- SET THE NAME OF YOUR DATABASE BELOW
    MyNewCopy
    set CHANGE_TRACKING = OFF
    --Remove the database level users from the database
    --these will be recreated after importing in SQL Server.
    declare
    @userSQL varchar(1000)
    set quoted_identifier off
    declare userCursor CURSOR for
    select 'DROP USER [' + name + ']'
    from sys.sysusers
    where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo'
    OPEN userCursor
    FETCH userCursor into @userSQL
    WHILE @@Fetch_Status = 0
    BEGIN
    exec(@userSQL)
    FETCH userCursor into @userSQL
    END
    CLOSE userCursor
    DEALLOCATE userCursor
    --Delete the SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.
    --We will run db synch later to recreate the correct view for SQL Server.
    if(1=(select 1 from sys.views where name = 'SYSSQLRESOURCESTATSVIEW'))
    DROP VIEW SYSSQLRESOURCESTATSVIEW
    --Next, set system parameters ready for being a SQL Server Database.
    update sysglobalconfiguration
    set value = 'SQLSERVER'
    where name = 'BACKENDDB'
    update sysglobalconfiguration
    set value = 0
    where name = 'TEMPTABLEINAXDB'
    --Clean up the batch server configuration, server sessions, and printers from the previous environment.
    TRUNCATE TABLE SYSSERVERCONFIG
    TRUNCATE TABLE SYSSERVERSESSIONS
    TRUNCATE TABLE SYSCORPNETPRINTERS
    --Remove records which could lead to accidentally sending an email externally.
    UPDATE SysEmailParameters
    SET SMTPRELAYSERVERNAME = ''
    GO
    UPDATE LogisticsElectronicAddress
    SET LOCATOR = ''
    WHERE Locator LIKE '%@%'
    GO
    TRUNCATE TABLE PrintMgmtSettings
    TRUNCATE TABLE PrintMgmtDocInstance
    --Set any waiting, executing, ready, or canceling batches to withhold.
    UPDATE BatchJob
    SET STATUS = 0
    WHERE STATUS IN (1,2,5,7)
    GO
    -- Clear encrypted hardware profile merchand properties
    update dbo.RETAILHARDWAREPROFILE set SECUREMERCHANTPROPERTIES = null where SECUREMERCHANTPROPERTIES is not null

    Remember to change the database namn on line 23

  3. Export the database
    cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin
    
    SqlPackage.exe /a:export /ssn:<server>.database.windows.net /sdn:MyNewCopy /tf:D:\Exportedbacpac\my.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /sp:<SQLAdmin password> /su:sqladmin

    Note: I usually store the DB backup in an Azure Storage Blog since this is the easiest way to move file between environments. See here

  4. Import the backup in the target environment
    cd C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin
    
    SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200

     

  5. When the database is imported you will need to prepare it for the new environment
    CREATE USER axdeployuser FROM LOGIN axdeployuser
    EXEC sp_addrolemember 'db_owner', 'axdeployuser'
    
    CREATE USER axdbadmin FROM LOGIN axdbadmin
    EXEC sp_addrolemember 'db_owner', 'axdbadmin'
    
    CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser
    EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'
    EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'
    
    CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser
    EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser'
    
    CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser
    EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser'
    EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser'
    
    CREATE USER axdeployextuser FROM LOGIN axdeployextuser
    EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser'
    
    CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]
    EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'
    
    UPDATE T1
    SET T1.storageproviderid = 0
        , T1.accessinformation = ''
        , T1.modifiedby = 'Admin'
        , T1.modifieddatetime = getdate()
    FROM docuvalue T1
    WHERE T1.storageproviderid = 1 --Azure storage
    
    ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON)
    GO
    DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking
    DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2
    GO
    -- Begin Refresh Retail FullText Catalogs
    DECLARE @RFTXNAME NVARCHAR(MAX);
    DECLARE @RFTXSQL NVARCHAR(MAX);
    DECLARE retail_ftx CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES
    	WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');
    OPEN retail_ftx;
    FETCH NEXT FROM retail_ftx INTO @RFTXNAME;
    
    BEGIN TRY
    	WHILE @@FETCH_STATUS = 0  
    	BEGIN  
    		PRINT 'Refreshing Full Text Index ' + @RFTXNAME;
    		EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';
    		SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';
    		EXEC SP_EXECUTESQL @RFTXSQL;
    		FETCH NEXT FROM retail_ftx INTO @RFTXNAME;
    	END
    END TRY
    BEGIN CATCH
    	PRINT error_message()
    END CATCH
    
    CLOSE retail_ftx;  
    DEALLOCATE retail_ftx; 
    -- End Refresh Retail FullText Catalogs
  6. Switch to using the new database
  7. Stop these services
    World Wide Web Publishing Service
    Management Reporter 2012 Process Service
    Microsoft Dynamics 365 Unified Operations: Batch Management Service
  8. Rename the old database to AxDB_orig
  9. Rename the new database to AxDB
  10. Start these services
    World Wide Web Publishing Service
    Microsoft Dynamics 365 Unified Operations: Batch Management Service
    Management Reporter 2012 Process Service
  11. Synchronize the database from Visual Studio

Links
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/copy-database-from-azure-sql-to-sql-server

 

 

Issues compiling label files

Yesterday evening (why does it always have to be evenings 😉 ) I was going to do a deploy of code to our staging environment. I started the build in Azure Devops and it failed:

C:\DynamicsSDK\Metadata\Microsoft.Dynamics.AX.Application.Build.targets(78,5): error MSB6006: “LabelC.exe” exited with code -1. [C:\DynamicsSDK\Metadata\BuildMetadata.proj]

OK… SO LabelC.exe atleast gives me some hints and since the deployconsisted of changes to Label Files this was not too strange. I downloaded the log file and found this information:

outlog=C:\DynamicsSDK\VSOAgent\_work\10\Logs\ModelName\Dynamics.AX.ModelName.labelc.log

So I looked in the log file on the build server and found the following:

The error was duplicate lines in two label files

Remove them by editing the file in Azure DevOps and check in the file. Restart the build and it worked… Way

Regression Suite Automation Tool: System.Exception: Microsoft (R) Visual C# Compiler version 4.6.1586.0 for C# 5

I had a really annoying issue today…

We are starting use Regression Suite Automation Tool (RSAT) for test automation in Dynamics 365 for Operations. This tool is using Task Recorder, Business Process Modeller and Azure Devops for automatically testing process in D365FO.

I needed to install it for one of my colleagues and run into this error message when I was trying to regenerate test files for a test case:

[BITS D365 RSAT] Create party record (external)
System.Exception: An error occurred while generating test cases. See details below. —> System.Exception: Microsoft (R) Visual C# Compiler version 4.6.1586.0
for C# 5
Copyright (C) Microsoft Corporation. All rights reserved.
This compiler is provided as part of the Microsoft (R) .NET Framework, but only supports language versions up to C# 5, which is no longer the latest version. For compilers that support newer versions of the C# programming language, see http://go.microsoft.com/fwlink/?LinkID=533240

c:\Temp\XXXX\MyTestCase.cs(253,56): error CS0266: Cannot implicitly convert type ‘Microsoft.Dynamics.TestTools.Dispatcher.Client.DispatchedForm’ to ‘MS.Dynamics.TestTools.DispatcherProxyLibrary.MyForm.MyGrid’. An explicit conversion exists (are you missing a cast?)

— End of inner exception stack trace —

My thoughts was that there was something wrong with my Dynamics 365 FO VM and I could not get it to work… after several frustrating hours I read a Yammer post that indicated that it might be an issue with the actual test case… go figure I had been testing with the same test case every single time. Once I switched test case everything worked… That is great… Wohoo

That is it for today

Johan

Looking at OData in Excel

In Dynamics 365 for Finance and Operations most of the data is represented in Entities which in turn are surfaced as OData feeds for used for integrations. If you need a raw look at the entity you can access the raw OData feed, unfortunately it is not easily readable for humans:

Excel to the rescue. In Excel go to Data – Get Data –  From Other Sources – From OData Feed

A lot more readable… right 🙂

That’s it for today

A fantastic Chrome addin if you are using D365FO

On of my primary tasks at work every day is maintaining multiple Dynamics 365 for Operations environments for my customer. This means that I often juggle a couple of environments at the same time and often I have to double check the URL to make sure I am in the right place.

There is of course the possibility to set different banners on each environment to be able to differentiate between them.

Today I found a wonderful tool that can help me, a chrome addin that puts a border at the edge of the window, in different color, depending on the URL 🙂

You configure the addin with different keywords which triggers the color switch The keywords can be URLs:

Or you can for instance use name of the legal entity:

Note that the keyword is Case sensitive. The result looks like this:

Since the addin doesn’t car what you are running in the browser you can use it to differentiate between other similar sites as well… as long as the URL differs in a consistent way.

If you are running the new Microsoft Edge (nased on Chromium… It works there as well 🙂

Link:
https://chrome.google.com/webstore/detail/urlcolors/jjccpcminoppplpmcfghflolejbdkekm

Screenshots in Task Recordings ni Dynamics 365 for Operations

The new Task Recorder in Dynamics 365 for Operations is a cool tool. It allows you to create a complete recording of a business process which then can be used for documentation, training and also for automated testing (I will revisit that in a later post). In this one we will take a closer look at documentation.

In AX 2012 the task recorder created nice screenshots in the word export but feature is no longer present in D365 for Operations. At least not in PU20 that my customer is running. There is however a solution.

If you use Google Chrome (which I do) there is actually a plugin that you can install and silently monitors you progress when you are creating you task recordings and takes a snapshot when you click on something. Once you save the recording as a Word Document these snapshots are inserted into the document.