Monday, April 26, 2021

MS D365 FinOps database sync failed with an error in SECURITYROLEPRIVILEGERESOURCELICENSEMAP

Scenario:

Database synchronization failed with following error message. 

It happened after I refreshed the tier 1 (DEV) environment of MS D365 FinOps from a sandbox environment database. The original database was named to AxDB_Orig as part of this refresh process.

Severity Code Description Project File Line Suppression State
Error ON JMAP.PRIVILEGEIDENTIFIER = SP.IDENTIFIER AND JMAP.ISUNIQUE = 1 0
Error LEFT JOIN SECURITYMENUITEMLICENSES RL 0
Error at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) 0
Error INSERT INTO SECURITYROLEPRIVILEGERESOURCELICENSEMAP 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options) 0
Error SP.RECID, 0
Error RP.AOTNAME, 0
Error at Microsoft.Dynamics.AX.Framework.Database.Synchronize.InitialSchemaSync.ScriptRegion.ExecuteCommand(SqlCommand cmd) 0
Error ON RL.IDENTIFIER = RP.AOTNAME 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync() 0
Error LEFT JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH PRMAP 0
Error END, 0
Error \n\nException message: System.Data.SqlClient.SqlException (0x80131904): Column name or number of supplied values does not match table definition. 0
Error FROM SECURITYPRIVILEGE SP 0
Error -- MAINTAINLICENSE 4 is Operations. We update MAINTAINLICENSE only if existing MAINTAINLICENSE is operations and set to Finance (8), SCM (9), Retail (10) 0
Error [MAINTAINLICENSE] = 0
Error ON PRMAP.SECURITYPRIVILEGE = SP.RECID 0
Error SP.IDENTIFIER, 0
Error --cleanup before populating data 0
Error \nCREATE PROCEDURE [DBO].[LICENSING_POPULATEROLEPRIVILEGELICENSEMAP] 0
Error --- End of inner exception stack trace --- 0
Error AS 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.InitialSchemaSync() 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.StaticSchema.RunStaticUpdate(String sqlConnectionString, String binDir, Boolean skipRegionHashing) 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync() 0
Error CASE WHEN JMAP.SKUNAME = 'Finance' AND RL.MAINTAINLICENSE = 4 THEN 8 0
Error TRUNCATE TABLE SECURITYROLEPRIVILEGERESOURCELICENSEMAP 0
Error LEFT JOIN LICENSINGSERVICEPLANSPRIVILEGE JMAP 0
Error RL.VIEWLICENSE 0
Error at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 0
Error END; 0
Error BEGIN 0
Error at Microsoft.Dynamics.AX.Framework.Database.Synchronize.InitialSchemaSync.RunSync() 0
Error SELECT DISTINCT PRMAP.SECURITYROLE, 0
Error at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 0
Error at Microsoft.Dynamics.AX.Framework.Database.Synchronize.InitialSchemaSync.ScriptRegion.Execute(SqlConnection connection, SqlTransaction transaction) 0
Error RL.MENUITEMTYPE, 0
Error Initialize schema failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Failed during InitialSchema at command: 0
Error ClientConnectionId:692f02f7-8690-4127-9da2-c59ead0da371 0
Error ON SP.IDENTIFIER = RP.PRIVILEGEIDENTIFIER AND SECURABLETYPE IN (1,2,3) 0
Error WHEN JMAP.SKUNAME = 'SCM' AND RL.MAINTAINLICENSE = 4 THEN 9 0
Error WHEN JMAP.SKUNAME = 'Retail' AND RL.MAINTAINLICENSE = 4 THEN 10 0
Error ELSE RL.MAINTAINLICENSE 0
Error at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 0
Error -- While updating we also consider Privilege in LicensePrivileges.json is unique. 0
Error at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 0
Error WHERE RL.MENUITEMTYPE IS NOT NULL 0
Error Error Number:213,State:1,Class:16 ---> System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition. 0
Error JOIN SECURITYRESOURCEPRIVILEGEPERMISSIONS RP 0
Error at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 0
Error syncengine.exe exited with code -1. 1   

Resolution:

It was the number of columns mismatch between the source and the target databases in SECURITYROLEPRIVILEGERESOURCELICENSEMAP

Executed this command against AxDB and AxDB_Original databases. 

sp_columns SECURITYROLEPRIVILEGERESOURCELICENSEMAP

Compared the number of columns in between both DBs and found AxDB has an extra column added as part of the refresh from sandbox database.

Open Object explorer and removed the additional column from SECURITYROLEPRIVILEGERESOURCELICENSEMAP



Synchronize the database in MS D365 FinOps and it completed successfully.

2 comments:

  1. Really thanks for sharing this useful post !! This post is very informative and I have got very good information.
    saas subscription management software

    ReplyDelete
  2. Thank you for valuable information.

    ReplyDelete

I will appreciate your comments !

MS D365 FinOps: How to create new LCS project and deploy Tier 1 (DEV) VM - Even you are not a MS customer or partner :) - Part I

Scenario: You are willing to work on Microsoft Dynamics 365 Finance Operations  product and want to get your hand dirty with some developmen...