[已解决] SECURITYROLEPRIVILEGERESOURCELICENSEMAP 数据库同步错误 / [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 Error List pane. Below is the error message(s).

Error message:

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         

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 than. Open the SSMS and select AxDB than search 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 you 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