Updating Dynamics 365 FO from 8.0 to 8.1 fails

When upgrading, sorry updating, a Dynamics 365 for Operations environment the runbook will fail somewhere around step 57 (I am not sure if it will be 57 every time). I have had this in every environment I have run… it might have to do with the fact that we are using the Project module

The error message will look like this:

The step 23 failed with the following error:
The step started
04/26/2019 13:16:35: Managed Sync Table Worker encountered an exception, but is continuing because ContinueOnError is true. Table Sync Failed for Table: ProjHierarchySorting. Exception: System.InvalidOperationException: Database execution failed: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_19940NODEREFERENCE’. The duplicate key value is (5637144576, usmf, 0, , 0). The statement has been terminated. CREATE UNIQUE INDEX I_19940NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); —> System.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_19940NODEREFERENCE’. The duplicate key value is (5637144576, usmf, 0, , 0). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.<ExecuteNonQuery>b__27_0(SqlCommand c) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc) — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo) at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) — End of inner exception stack trace — at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteTableModificationDdl(TableSyncSqlBatch sqlBatch, String tableName, Boolean disableChangeTracking) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterTable(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterViewAndHandlePossibleExceptionsFromSchemaBoundViews(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTable(AxTable newTableSchema, AxTable oldTableSchema, Int32 tableId) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ModifyTable(AxTable newTableSchema, Dictionary`2 oldTableSchemaDictionary) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.TableSyncAction(AxTable axTable, Dictionary`2 oldTableSchemaDictionary, ConcurrentBag`1 exceptions) 04/26/2019 13:20:47: AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Full sync did not complete successfully. Error: AggregateException:One or more errors occurred. at Microsoft.Dynamics.AX.Data.Management.ManagedSyncWorkerManager.SyncAll() at Microsoft.Dynamics.AX.Data.Management.ManagedSync.<SyncAll>b__21_0() at Microsoft.Dynamics.AX.Data.Management.ManagedSync.EnsureMetadataProviderCacheCleared(Action action) at Microsoft.Dynamics.AX.Data.Management.ManagedSync.SyncAll() at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullManagedSync(SyncOptions options, String sqlConnectionString, IMetadataProvider metadataProvider) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c__DisplayClass15_0.<RunFullTableSync>b__0() at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler) Inner exceptions: Database execution failed: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_19940NODEREFERENCE’. The duplicate key value is (5637144576, usmf, 0, , 0). The statement has been terminated. CREATE UNIQUE INDEX I_19940NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteTableModificationDdl(TableSyncSqlBatch sqlBatch, String tableName, Boolean disableChangeTracking) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterTable(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterViewAndHandlePossibleExceptionsFromSchemaBoundViews(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTable(AxTable newTableSchema, AxTable oldTableSchema, Int32 tableId) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ModifyTable(AxTable newTableSchema, Dictionary`2 oldTableSchemaDictionary) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.TableSyncAction(AxTable axTable, Dictionary`2 oldTableSchemaDictionary, ConcurrentBag`1 exceptions). at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunFullTableSync>b__15_1(Tuple`2 result) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullTableSync(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider) at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullTableViewSync() at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync() at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync(SyncOptions options) at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options) 04/26/2019 13:20:47: The operation failed.
The step failed
at Microsoft.Dynamics.AX.AXUpdateInstallerBase.AXUpdateInstallerBase.executeParallel(String runbookID, Boolean silent, String updatePackageFilePath, IRunbookExecutor runbookExecutor, Boolean versionCheck, Parameters param)
at Microsoft.Dynamics.AX.AXUpdateInstaller.Program.InstallUpdate(String[] args)
at Microsoft.Dynamics.AX.AXUpdateInstaller.Program.Main(String[] args

The issue is that the update adds an index to the table ProjHierarchySorting and if there are records in this table it will most likely fail since there will me posts that the index considers duplicates.

The solution is to empty this table before the update (this can be done safely since the table is auto populated). If you have already started the update you can empty the table and then resume the update. Simply log into the server, start Management Studio and run:

delete from ProjHierarchySorting

In production you will need to add a message in the service request telling the technician to run the above SQL line.

My entities are missing…

One of the crucial concepts in Dynamics 365 for Operations are Data Entities… you use them for basically everything. Data entities is an abstraction on top of the regular database table which aggregates multiple table into one object.

Today I had a problem with an entity missing som the entity list. I this happens the solution is usually to refresh the list by going to  System administration – Workspaces – Data management – Data import/export framework parameters and clicking Refresh entity list under Entity Settings.

That did not do the trick today so I had to dig deeper… and found this SQL script that clears the entity list:

-- This source code or script is freeware and is provided on an “as is” basis without warranties of any kind,
-- whether express or implied, including without limitation warranties that the code is free of defect,
-- fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of
-- the code is with the end user.

-- Create backups of the tables first

SELECT *
INTO DMFEntity_backup
FROM DMFEntity
GO

SELECT *
INTO DMFTargetXML_backup
FROM DMFTargetXML
GO

SELECT *
INTO DMFTargetXMLToEntityMap_backup
FROM DMFTargetXMLToEntityMap
GO

SELECT *
INTO DMFTargetEntityHierarchy_backup
FROM DMFTargetEntityHierarchy
GO

-- Truncate the tables
TRUNCATE TABLE DMFENTITY
GO

TRUNCATE TABLE DMFTargetXML
GO

TRUNCATE TABLE DMFTargetXMLToEntityMap
GO

TRUNCATE TABLE DMFTargetEntityHierarchy
GO

Then I go back to Data import/export framework parameters and run Refresh entity list.

That seem to do the trick

NOTE: this procedure has one slight down side and that you are also deleting all mappings for all entities. The (rather tedious) solution is to go into each entity and regenerate mappings… for all 2800+ entities… Wohoo…

Fortunately a smart person on Yammer wrote this script that regenerates all mappings for all entities in one run.

Links:
https://cloudblogs.microsoft.com/dynamics365/no-audience/2016/07/20/data-management-missing-entities-2/?source=axsupport

Problems accessing the /reports website in Dynamics 365 VMs

Hi

Today I had an issue trying to access the classic reports folder for SSRS in a Dynamics 365 for Finance and Operations VM hosted in Azure. When connecting to the site we used http://[VMname]/reports and all we get is a login prompt.

This one was quite easy… Just fire up the Reporting Services Configuration Manager, goto Web Portal URL and click Apply to configure it

And that is it!

Happy weekend

/Johan

Copy database from a OneBox environment to another OneBox Environment

This document is a check-list I have compiled on moving a database from one OneBox environment to another. Microsoft does not have a document for this scenario so I have compiled information from multiple blog posts.

1. Create a Backup copy of the Source database

2. Move the Database to the destination environment and restore it there

3. Stop the AX Services

  • World wide web publishing service (on all AOS computers)
  • Microsoft Dynamics 365 for Finance and Operations Batch Management Service
    (on non-private AOS computers only)
  • Management Reporter 2012 Process Service (on business intelligence [BI]
    computers only)

4. Rename the OLD database to AxDB_ORIG_TodaysDate and rename the newly restored database to AxDB.

5. Start the services that was stopped in Step 3 above

6. Sync the Database using either Visual Studio or this command from an elevated command prompt:

K:<br />
cd K:\AosService\WebRoot\bi<br />
Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "K:\AosService\PackagesLocalDirectory" -metadatadir K:\AosService\PackagesLocalDirectory -sqluser axdbadmin –sqlserver localhost -sqldatabase AxDB -setupmode sync -syncmode fullall -isazuresql false -sqlpwd <sql password> >log.txt 2>&1

Note: K is the Service Volume

Verify that there are no errors in log.txt

7. I the environment is running Retail you will need to run the Retail Reprovisioning tool in this document.

8. Reset the Financial Reporting Database according to this document.

Links
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/reset-financial-reporting-datamart-after-restore
http://blog.johanpersson.nu/?p=2684

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column when syncing Retail Data to Channel Database

One of our customer are going to use the AX 2012 R3 Retail solution and the setup of the Async Server – Client in the TEST environment was setup and tested. The application consultants started importing the product data to AX and suddenly the sync stopped with this error:

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.connectionString: Data Source=XXXXXX;Initial Catalog=ClientChannelDB;Integrated Security=True;Connect Timeout=60;Application Name=”Commerce Data Exchange Async Client” —> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. —> System.InvalidOperationException: String or binary data would be truncated.

After having a look online I found that there is no sync of the schema between the AX database and the Channel Database (!!!). This meant that when we imported data to AX everything worked but when we did the sync jobs it failed. The reason being that some field types did not match.

After talking to MS support I got a script to try to figure out which fields were wrong

select t1.table_name, t1.column_name, t1.character_maximum_length, t2.character_maximum_length
from MicrosoftDynamicsAX.INFORMATION_SCHEMA.columns t1
inner join RetailHoustonStore.INFORMATION_SCHEMA.columns t2
on t1.table_name = t2.table_name
and t1.column_name = t2.column_name
and t1.character_maximum_length != t2.character_maximum_length order by TABLE_NAME

this query finds all of the fields which are not matched between the AX database and the Channel Database. With some help we found that the issue probably was the field called RETAILVARIANTID which was present in INVENTDIMCOMBINATION and INVENTITEMBARCODE

We ran these SQL queries:

ALTER TABLE [ClientChannelDB].ax.INVENTDIMCOMBINATION ALTER COLUMN RETAILVARIANTID nvarchar(25) NOT NULL;
ALTER TABLE [ClientChannelDB].ax.INVENTITEMBARCODE ALTER COLUMN RETAILVARIANTID nvarchar(25) NOT NULL;

And there was also a view called INVENTDIMCOMBINATION which we scripted out to a query windows, deleted and recreated again. This solved the issue.

Bryta sig in i en SQL Server

Härom dagen bytte jag domän på en SQL server och missade att byta lösenord på SA innan jag gjorde det. Detta betydde att jag jag inte kunde logga in. Här kommer lite information om hur jag gjorde för att ta mig in.

För att göra en Password Recovery på SQL Servern gjorde jag följande

  1. Starta SQL Configuration Manager
  2. Stoppa tjänsten SQL Server
  3. Öppna Properties för SQL Server tjänsten
  4. Gå till fliken Advanced
  5. Ändra Startup Parameters till –m;-dC:\Program Files\Microsoft SQL…
  6. Starta SQL tjänsten igen
  7. Starta en Kommando Prompt
  8. Kör SQLCMD
  9. Kör följande SQL kommandon för att skapa ett nytt SA konto och ge det rättigheterna sysadmin:
       1: CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy

       2: go

       3: sp_addsrvrolemember ‘recovery’, ‘sysadmin’

       4: go

  10. Gå tillbaka till SQL Configuration Manager stäng SQL tjänsten
  11. Öppna Properties för SQL Server tjänsten
  12. Gå till fliken Advanced
  13. Ändra Startup Parameters tillbaka till -dC:\Program Files\Microsoft SQL…
  14. Starta SQL tjänsten igen
  15. Starta upp SQL Server Management Studio och ändra lösenordet på sa kontot

Jag i det här läget att server var i Windows Authentication mode så jag var tvungen att ställa om den till mixed mode.

  1. Starta Regedit
  2. Ändra HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode till 2 för mixed mode

 

 

Länkar

http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

http://www.sqlservercentral.com/Forums/Topic421197-149-1.aspx#bm505642

Flytta Business Contact manager till en ny server

Här kommer en liten lista på saker man behöver tänka på när man skall flytta Microsoft Business Contact Manager till en ny SQL Server

  • SQL instansen måste heta MSSMLBIZ
  • SQL instansen måste vara inställd att lyssna på port
  • SQL Browser måste vara startad
  • Se till att Windows Brandvägg är öppen på rätt portar
  • SQL Servern måste vara inställd för remote connections

Länkar

http://support.microsoft.com/kb/901164
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
http://support.microsoft.com/kb/823938
http://msdn.microsoft.com/en-us/library/ms177440.aspx
http://social.technet.microsoft.com/Forums/en/outlook/thread/fbc8e91b-93bc-4c49-888f-7785b2a2680a
http://support.microsoft.com/default.aspx?scid=kb;EN-US;953745
http://social.technet.microsoft.com/Forums/en/outlook/thread/fbc8e91b-93bc-4c49-888f-7785b2a2680a
http://wintivity.wigital.net/blog/2008/07/26/sql-database-remote-install-business-contact-manager-office-accounting-point-of-sale-domain/

Finding out version and edition of a Microsoft SQL Server

The other day at a customers I needed to find out the version and edition of an installed SQL server… Here is the solution:

Connect to an instance of SQL Server by using the following sqlcmd command:

sqlcmd -S ServerInstance

Where Server is the name of the computer and Instance is the name of the instance you want to check. If you have used the default named instance during setup, specify the instance as “SQLExpress”.

To identify the server name and instance name, type the following command:

select @@servername
go

When connected, type the following two commands:

select @@version
go

Alternatively, you can type the following two commands:

select serverproperty(‘edition’)
go

The sqlcmd tool displays the version information. If the last line of information includes “Express Edition,” the instance to which you are connecting is running SQL Server Express.

Thanks Anders…

Links

http://msdn.microsoft.com/en-us/library/ms165662(SQL.90).aspx

Reports not working in Microsoft CRM

When you try to open the Reports folder in Microsoft CRM you get the error:

Error An error has occurred. For more information, contact your system administrator.

This happens sometimes if you have separated CRM, SQL and reporting Services

This hapaned on a clientcomputer with Windows 2000. The solution was to enable Integrated Windows Authentication in Internet Explorer under “Internet Options – Advanced – Security Section”

Links

http://microsoftdynamicscrm.blogspot.com/2007/11/error-message-when-you-try-to-access.html
http://www.microsoft.com/downloads/details.aspx?FamilyID=51bf9f20-bd00-4759-8378-b38eefda7b99&DisplayLang=en

GrundläggandeMaintenance Tasks på SQL Server för icke-DB-Admins

Basic install

– Database Components
– Integration Service
– Management tools

Maintenance Plans

Maintenance plan (Kräver Integration Services)

Skapa Backupjobb (Scheduleras dagligen innan så att den är klar innan bandbackup)

Dra in ett Database backup block

  • Välj Databaser
  • Välj Typ
  • Välj Destination

Dra in ett Maintenance Cleanup Task block

  • Välj Backup Files
  • Välj mapp
  • Ställ in “Delete files based on…” till 3 dagar

Dra in ett “History Cleanup Task” block

  • Välj “Local Server Connection”
  • Välj “Backup and Restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance Plan History”
  • Ställ in tid (ex 4 veckor)

Koppla ihop blocken enligt: Backup – Maintenance Cleanup – History Cleanup

Skapa Indexering och Verifiering (Scheduleras en gång i veckan)

Dra in “Rebuild Index Task” block

  • Välj Databaser
  • Välj “Reorganize pages…”

Dra in “Update Statistics Task” block

  • Välj Databaser
  • Välj “All existing statistics”

Dra in “History Cleanup Task” block

  • Välj “Backup and restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance plan history”
  • Ställ in tid (ex 4 weeks)

Koppla ihop blocken enligt: Rebuild – Update Stats – History Cleanup

Check Database Integrity (Scheduleras en gång i veckan efter Indexering och verifiering)

Dra in “Check Database Integrity task” block

  • Välj databaser
  • Välj Include indexes

Dra in “History Cleanup Task”

  • Välj “Backup and restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance plan history”
  • Ställ in tid (ex 4 weeks)