June 21, 2021

[Solved] SECURITYROLEPRIVILEGERESOURCELICENSEMAP DB sync error

Hi Folks,

The other day I was getting errors while doing build and DB sync. This error is quite strange when looking at the details in the Error List pane. Below is the error message(s).

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

Error message:

         

 

 

If you succeed to get some details from this error message 😊, you will see the issue with SECURITYROLEPRIVILEGERESOURCELICENSEMAP while you won’t get anything in AOT with this name.





 

 





Let’s try the SQL then. Open the SSMS and select AxDB then searches for dbo. SECURITYROLEPRIVILEGERESOURCELICENSEMAP and right-click to see the design. You may get some additional fields here, ideally, there should be below seven fields, (you should be double sure if anyone else in your team added anything)

And delete any additional fields from here.

 



 





Save your changes and go back to Visual Studio and try to do build and DB synch. It should work.


Enjoy!!!

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

May 20, 2021

QuickFix: Entity store doesn't refresh

Issue: I have made a change in an entity store(Power BI measures) but while refreshing it from System Admin> Entity store> select entity > hit refresh button.

 

Possible reason: Check if the batch service is up and running, try to restart the same. 

 

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta