Hi I am getting very similar sync error.
System.InvalidOperationException: System.InvalidOperationException: Database execution failed: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
[IF OBJECTPROPERTY(OBJECT_ID('HCMJOBENTITYINSERTTRIGGER'), 'ISTRIGGER') = 1
DROP TRIGGER HCMJOBENTITYINSERTTRIGGER
]
[CREATE TRIGGER HCMJOBENTITYINSERTTRIGGER ON HCMJOBENTITY INSTEAD OF INSERT AS BEGIN
INSERT INTO HCMJOB (JOBID,MAXIMUMPOSITIONS,I.RECVERSION,I.PARTITION)
SELECT COALESCE(I.JOBID, ''),
COALESCE(I.MAXIMUMNUMBEROFPOSITIONS, 0),
0,
I.PARTITION FROM INSERTED I
WHERE NOT EXISTS (SELECT 'x' FROM HCMJOB WHERE (JOBID = I.JOBID) AND (PARTITION = I.PARTITION))
AND ( I.JOBID <> '')
GROUP BY I.JOBID,
I.MAXIMUMNUMBEROFPOSITIONS,
I.PARTITION
INSERT INTO HCMJOBDETAIL (DEFAULTFULLTIMEEQUIVALENCY,DESCRIPTION,EXTERNALSURVEYCODE,MARKETCONTROLPAY,MARKETMAXIMUMPAY,MARKETMINIMUMPAY,MARKETSOURCE,NOTE,VALIDFROM,VALIDTO,JOB,COMPENSATIONLEVEL,JOBFUNCTION,JOBTYPE,SURVEYCOMPANY,TITLE,I.RECVERSION,I.PARTITION)
SELECT COALESCE(I.FULLTIMEEQUIVALENT, 0),
COALESCE(I.DESCRIPTION, ''),
COALESCE(I.COMPENSATIONREFERENCEJOB, ''),
COALESCE(I.MARKETPRICECONTROLPOINT, 0),
COALESCE(I.MARKETPRICEHIGHTHRESHOLD, 0),
COALESCE(I.MARKETPRICELOWTHRESHOLD, 0),
COALESCE(I.MARKETPRICESOURCE, ''),
COALESCE(I.JOBDESCRIPTION, ''),
COALESCE(I.EFFECTIVE, ''),
COALESCE(I.EXPIRATION, ''),
COALESCE(I.NOTE, ''),
COALESCE((SELECT TOP 1 RECID FROM HCMJOB WHERE (JOBID = I.JOBID) AND (PARTITION = I.PARTITION)), 0),
COALESCE((SELECT TOP 1 RECID FROM HCMCOMPENSATIONLEVEL WHERE (COMPENSATIONLEVELID = I.COMPENSATIONLEVELID) AND (PARTITION = I.PARTITION)), 0),
COALESCE((SELECT TOP 1 RECID FROM HCMJOBFUNCTION WHERE (JOBFUNCTIONID = I.FUNCTIONID) AND (PARTITION = I.PARTITION)), 0),
COALESCE((SELECT TOP 1 RECID FROM HCMJOBTYPE WHERE (JOBTYPEID = I.JOBTYPEID) AND (PARTITION = I.PARTITION)), 0),
COALESCE((SELECT TOP 1 RECID FROM HCMSURVEYCOMPANY WHERE (SURVEYCOMPANYID = I.COMPENSATIONSURVEYCOMPANYID) AND (PARTITION = I.PARTITION)), 0),
COALESCE((SELECT TOP 1 RECID FROM HCMTITLE WHERE (TITLEID = I.TITLEID) AND (PARTITION = I.PARTITION)), 0),
GROUP BY I.FULLTIMEEQUIVALENT,
I.DESCRIPTION,
I.COMPENSATIONREFERENCEJOB,
I.FUNCTION_,
I.MARKETPRICECONTROLPOINT,
I.MARKETPRICEHIGHTHRESHOLD,
I.MARKETPRICELOWTHRESHOLD,
I.MARKETPRICESOURCE,
I.JOBDESCRIPTION,
I.COMPENSATIONSURVEYCOMPANY,
I.EFFECTIVE,
I.EXPIRATION,
I.NOTE,
I.JOBID,
I.COMPENSATIONLEVELID,
I.FUNCTIONID,
I.JOBTYPEID,
I.COMPENSATIONSURVEYCOMPANYID,
I.TITLEID,
END
[IF OBJECTPROPERTY(OBJECT_ID('HCMJOBENTITYUPDATETRIGGER'), 'ISTRIGGER') = 1
DROP TRIGGER HCMJOBENTITYUPDATETRIGGER
[CREATE TRIGGER HCMJOBENTITYUPDATETRIGGER ON HCMJOBENTITY INSTEAD OF UPDATE AS BEGIN
IF (UPDATE(JOBID) OR UPDATE(MAXIMUMNUMBEROFPOSITIONS))
BEGIN
UPDATE HCMJOB SET
JOBID = I.JOBID,
MAXIMUMPOSITIONS = I.MAXIMUMNUMBEROFPOSITIONS
FROM INSERTED I WHERE I.RECID = HCMJOB.RECID
IF (UPDATE(COMPENSATIONLEVEL) OR UPDATE(FULLTIMEEQUIVALENT) OR UPDATE(DESCRIPTION) OR UPDATE(COMPENSATIONREFERENCEJOB) OR UPDATE(FUNCTION_) OR UPDATE(JOBTYPE) OR UPDATE(MARKETPRICECONTROLPOINT) OR UPDATE(MARKETPRICEHIGHTHRESHOLD) OR UPDATE(MARKETPRICELOWTHRESHOLD) OR UPDATE(MARKETPRICESOURCE) OR UPDATE(JOBDESCRIPTION) OR UPDATE(COMPENSATIONSURVEYCOMPANY) OR UPDATE(TITLE) OR UPDATE(EFFECTIVE) OR UPDATE(EXPIRATION) OR UPDATE(NOTE) OR UPDATE(JOBID) OR UPDATE(MAXIMUMNUMBEROFPOSITIONS) OR UPDATE(COMPENSATIONLEVELID) OR UPDATE(FUNCTIONID) OR UPDATE(JOBTYPEID) OR UPDATE(COMPENSATIONSURVEYCOMPANYID) OR UPDATE(TITLEID))
UPDATE HCMJOBDETAIL SET
JOB = COALESCE((SELECT TOP 1 RECID FROM HCMJOB WHERE (JOBID = I.JOBID) AND (PARTITION = I.PARTITION)), 0),
COMPENSATIONLEVEL = COALESCE((SELECT TOP 1 RECID FROM HCMCOMPENSATIONLEVEL WHERE (COMPENSATIONLEVELID = I.COMPENSATIONLEVELID) AND (PARTITION = I.PARTITION)), 0),
JOBFUNCTION = COALESCE((SELECT TOP 1 RECID FROM HCMJOBFUNCTION WHERE (JOBFUNCTIONID = I.FUNCTIONID) AND (PARTITION = I.PARTITION)), 0),
JOBTYPE = COALESCE((SELECT TOP 1 RECID FROM HCMJOBTYPE WHERE (JOBTYPEID = I.JOBTYPEID) AND (PARTITION = I.PARTITION)), 0),
SURVEYCOMPANY = COALESCE((SELECT TOP 1 RECID FROM HCMSURVEYCOMPANY WHERE (SURVEYCOMPANYID = I.COMPENSATIONSURVEYCOMPANYID) AND (PARTITION = I.PARTITION)), 0),
TITLE = COALESCE((SELECT TOP 1 RECID FROM HCMTITLE WHERE (TITLEID = I.TITLEID) AND (PARTITION = I.PARTITION)), 0),
DEFAULTFULLTIMEEQUIVALENCY = I.FULLTIMEEQUIVALENT,
DESCRIPTION = I.DESCRIPTION,
EXTERNALSURVEYCODE = I.COMPENSATIONREFERENCEJOB,
MARKETCONTROLPAY = I.MARKETPRICECONTROLPOINT,
MARKETMAXIMUMPAY = I.MARKETPRICEHIGHTHRESHOLD,
MARKETMINIMUMPAY = I.MARKETPRICELOWTHRESHOLD,
MARKETSOURCE = I.MARKETPRICESOURCE,
NOTE = I.JOBDESCRIPTION,
VALIDFROM = I.EFFECTIVE,
VALIDTO = I.EXPIRATION
FROM INSERTED I WHERE I.RECID#2 = HCMJOBDETAIL.RECID
[IF OBJECTPROPERTY(OBJECT_ID('HCMJOBENTITYDELETETRIGGER'), 'ISTRIGGER') = 1
DROP TRIGGER HCMJOBENTITYDELETETRIGGER
[CREATE TRIGGER HCMJOBENTITYDELETETRIGGER ON HCMJOBENTITY INSTEAD OF DELETE AS BEGIN
DELETE HCMJOBDETAIL FROM HCMJOBDETAIL JOIN DELETED D ON D.RECID#2 = HCMJOBDETAIL.RECID
DELETE HCMJOB FROM HCMJOB JOIN DELETED D ON D.RECID = HCMJOB.RECID
WHERE NOT EXISTS (SELECT 'x' FROM HCMJOBDETAIL C WHERE C.JOB = HCMJOB.RECID)
---> System.Data.SqlClient.SqlException: ---> System.Data.SqlClient.SqlException: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
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.Dynamics.AX.Framework.Database.Synchronize.SqlExecute.Execute(IEnumerable`1 commands)
at Microsoft.Dynamics.AX.Framework.Database.Tools.DataEntityTriggerSync.RunSync()
--- End of inner exception stack trace ---
at Microsoft.Dynamics.AX.Framework.Database.Tools.DataEntityTriggerSync.ThrowOnSqlExecuteException(SqlException e, String commandText)
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.DataEntityTriggerSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.PartialTableViewSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.PartialSync()
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)
Inner Exception: Inner Exception: System.Data.SqlClient.SqlException (0x80131904): The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
ClientConnectionId:b11482a3-80f4-41c6-a7c3-adc1b2a599e9
Error Number:121,State:1,Class:15
Process: syncengine.exe exited with code -1.
