MS D365 FinOps database sync failed with an error in SECURITYROLEPRIVILEGERESOURCELICENSEMAP
Faisal Fareed
10,794
User Group Leader
Like
Share
ReportScenario:
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
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.
*This post is locked for comments