Microsoft Dynamics AX (Archived)

Synchronization errors

(0) Share

Report

Posted on by kvnkk

800

Hi,

I just create new filed in Ledger Journal table getting below error

Can any one suggest me on below errors pls :-

1738.Untitled.jpg

Error Synchronize database Cannot execute a data definition language command on ().
The SQL database has issued an error.
Info Synchronize database SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'I_211TYPENUMIDX' is dependent on column 'JOURNALNUM'.
Info Synchronize database SQL statement: ALTER TABLE "DBO".LEDGERJOURNALTABLE ALTER COLUMN JOURNALNUM nvarchar(20) NOT NULL
Error Synchronize database Problems during SQL data dictionary synchronization.
The operation failed.
Info Synchronize database Synchronize failed on 1 table(s)

Error 2:- Added new field on Agreement table and enable alert rule on field. (error coming when alert getting generated)

[Microsoft][SQL Server Native Client 11.0][SQL Server]A column has been specified more than once in
the order by list. Columns in the order by list must be unique.. The SQL statement was: "SELECT T1.PURCHNUMBERSEQUENCE,T1.VENDACCOUNT,T1.VENDORDATAAREAID,T1.BUYINGLEGALENTITY,T1.WORKFLOWSTATUS_PSN,T1.INSTANCERELATIONTYPE,T1.AGREEMENTCLASSIFICATION,T1.AGREEMENTSTATE,T1.DOCUMENTTITLE,T1.DOCUMENTEXTERNALREFERENCE,T1.CURRENCY,T1.DEFAULTAGREEMENTLINETYPE,T1.DEFAULTAGREEMENTLINEEFFECTIVEDATE,T1.DEFAULTAGREEMENTLINEEXPIRATIONDATE,T1.ORIGINATOR,T1.LANGUAGE,T1.EARLIESTLINEEFFECTIVEDATE,T1.LATESTLINEEXPIRATIONDATE,T1.ISDELETED,T1.DEFAULTDIMENSION,T1.AWARDDATE,T1.DEPARTMENT,T1.PROJECTMANAGER,T1.TOTALAMOUNT,T1.BOOKMARK1_4895,T1.PURCHAGREEMENTSTATUS,T1.DEFAULTAGREEMENTLINEEXPIRATIONDATE1_4895,T1.MODIFIEDDATETIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,T1.RECVERSION,T1.RELATIONTYPE,T1.PARTITION,T1.RECID FROM AGREEMENTHEADER T1 WHERE (((T1.PARTITION=?) AND (T1.RECID=?)) AND (T1.INSTANCERELATIONTYPE IN (4902) )) ORDER BY T1.RECID,T1.RECID OPTION(FAST 2)"

Regards,

KVNKK

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Martin Dráb 237,884 Most Valuable Professional on at

Like (0)

Report

You said that you merely created a new field but synchronization is trying to modify JournalNum field. Are you sure there aren't any changes?

Was this reply helpful? Yes No
kvnkk 800 on at

Like (0)

Report

Hi Martin,

I just created new string field that's it. I didn't do any change. As soon as when I am saving the table got the sync error and later deleted field from table even though the issue same.

In test environment there was no issue.

Was this reply helpful? Yes No
PA-22040759-0 6,194 on at

Like (0)

Report

Is there possibly some sort of mismatch in SQLDictionary with field ids between JournalNum and your new field?

Was this reply helpful? Yes No
kvnkk 800 on at

Like (0)

Report

Yes Palle Agermark. There are chances, But how to check? Any suggestions?? . The new field no more exist in the table but still issue is same.

Will AOS restart will solve the issue??

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

Check SQLDictionary table and compare to the properties of the table/fields that you see in the MorphX IDE.

Did you by any change import a database into this environment from another environment? If yes, that explains why the ids would be out of sync. In that case you need to copy both databases (business and model). You can copy business database only between environments where object ids are the same.

Was this reply helpful? Yes No
Suggested answer

PA-22040759-0 6,194 on at

Like (0)

Report

Nikolaos has good point about how this issue could have come around.

It sounds like you have an entry in SQLDictionary where your new field has the ID of what should have been the JournalNum field.

What kind of box is this on; dev, test, prod?

Was this reply helpful? Yes No
kvnkk 800 on at

Like (0)

Report

Hi Palle Agermark/Nikolaos,

Issue in Production environment.

On urgent basis I just added a string field manually in LedgerJournalTable directly in production. (We did it previously in different tables but never faced any issues.)

As as soon as when I added the string field automatically table got compiled, saved and synchronize and got an error.

Later I have removed the field from table even though issue is same.( Before performing in PROD have did it in TestEnvi and everything looks fine then I have done in PROD)

As per your suggestion I have checked in SqlDictionary and compared with other environment and didn't find any change.

Table id : 211(LEDGERJOURNALTABLE)

Field id : 1(JOURNALNUM)

Also I have compare ids between SysDictTable and SqlDictionary and no change in field and table ids.

Note: - Recently we didn't copy any database or model from other environments.( Still we are doing code movement in XPO format not with models) Almost from last three years same database and model.(Only moved customized objects)

Regards,

KVNKK

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

Maybe this issue was there already before you tried to add the new field? You wrote "As soon as when I am saving the table got the sync error and later deleted field from table even though the issue same."

When is the last confirmed succesful compile and sync performed in your production environment?

You should not create new fields manually in production environment. Also xpo deliveries are not recommended since it's almost the same as developing manually in the prod environment.

The safest deployment method is to use modelstore delivery. That's the only way to know that you have a compilable (and compiled) solution already before starting the deployment.

Definetely there can be sync errors even with modelstore delivery, but those would be catched in preprod/UAT already, if you are testing there with a copy of production database.

How to resolve your current issue? The first thing would be to check if there are any changes in JournalNum field (does that field exist in your customization layer?).

Was this reply helpful? Yes No
kvnkk 800 on at

Like (0)

Report

Hi Nikolaos,

Yes you are correct. May be issue already exist on the table but i didn't noticed. I have verified the table field Journalnum doesn't have any changes in other layer.(we have only one customized layer that is USR- Production)

Ref screen :-

Sync and full compilation done only at the time of Go-Live (Almost 2-year back).

Regards,

KVNKK

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

I have to say that your production deployment process is not in line with MS best practices and I'm not surprised that there are strange problems.

Please check this white paper about the topic: www.microsoft.com/.../details.aspx

Basically you don't know when the environment was last in a healthy state. This is something that you have to check with each production deployment! And you should deploy modelstore (or at least models), not xpos

If I were responsible for fixing the environment, I would first copy the databases to another AX instance, so I could try different things without causing more harm in the production. After figuring out how to fix it, I would plan how to use this info to fix the prod environment, too.

After prod is fixed, I would copy the databases one more time and use the copy environment as a staging environment for prod deployments from then on. And I would start using modelstore delivery for production deployments.

Was this reply helpful? Yes No