Error during data dictionary synchronization: Table mistaken for View

(0) Share

Report

Posted on by Community Member

In DEV environment AOT we have a table called
LedgerTrvExpTransCube

In DEV environment SQL we have a view called
LEDGERTRVEXPTRANSCUBE

The object is not modified.

Now, when we execute full Data Dictionary synchronization, we get following error:
Cannot execute a data definition language command on ().
The SQL database has issued an error.
SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot alter 'LEDGERTRVEXPTRANSCUBE' because it is not a table.
SQL statement: ALTER TABLE LEDGERTRVEXPTRANSCUBE DROP COLUMN PARTITION#2

I am confused how something like this could happen.

In all other environments there is the view in the AX AOT called LedgerTrvExpTransCube and view on the SQL server. According to TechNet this object is a view. So I wonder why we see it as a table in our DEV AOT...

The versions of the Microsoft models are all the same between these two environments.

Other info: the table SqlSyncInfo is empty.

Any ideas how it could have happened and how it could be solved?
I am thinking about dropping the view on SQL and sync again.

Thanks,

Waldemar

*This post is locked for comments

I have the same question (0)

All responses (19)

Answers (5)

Mariano Gracia on at

Like (0)

Report

I had the same problem but with another view, my problem was the InventValueTransView, it was located in the tables nodes, but in fact it is a view, not a table, I couldn't solve my problem so I exported my model into another environment (that application wasn't on production). I hope someone else helps you, because I always wanted to know what happened and why

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

yeah, not sure what kind of black magic is going on here.

I just checked all other environments and everything is fine.

It is just our DEV where it happened.

And again, those objects were not modified.

I am going to dig deeper into the ModelStore and see if I find something.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can you check the table SQLDictionary? Search for your table and check the field "Flags". If it is "1" for some reason it is taken as a view....tables are "0" as i understand. I experienced similar strange vodoo. Maybe AX choked on somthing :-)

Greetings

Nils

Was this reply helpful? Yes No
Mariano Gracia on at

Like (0)

Report

In my case I did delete the sqldictionary related to the view, but it didn't solve my problem, what I think is that the problem is in the model... which is even worst, but it is only my opinion

Was this reply helpful? Yes No
Verified answer

Tommy Skaue Moderator on at

Like (0)

Report

Most likely they are sharing the same ID causing the conflict. The easiest solution is to simply drop the view and resync it. Since it is a view, there is no risk dropping it and reestablishing it. You do not need to stop anything, just drop the view from the database and head back to the AOT and sync the view.

Was this reply helpful? Yes No
Mariano Gracia on at

Like (0)

Report

In my case I couldn't drop the view because it is in the sys layer...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@Mariano, I think Tommy meant to drop the view from the SQL server.

@Tommy, yes, I had this thought too. My only concern is, since in AOT it is a table, won't it be synced as table again to SQL?

@Nils, in the SqlDictionary the flag is set to "1", so I hope after I drop the view on SQ, it will be synced as view and not as table (as shown in AOT)

Was this reply helpful? Yes No
Mariano Gracia on at

Like (0)

Report

Waldemar, I hope deleting the view from sql works, in my case it didn't worked, I deleted the view (drop view...) and the registers from the sqldictionary related to the view, but again, Dynamics did thrown an error when I sinchronized the database

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Guys, I have dropped the view from the SQL and synchronized the entire AOT again.

The error is gone, but the object LEDGERTRVEXPTRANSCUBE is still shown as table in AX. Now it also appears under tables in SQL, instead of views.

Note, in all other environments it is a view and TechNet says it is a view too.

The flag in SqlDictionary changed to '0'.

We are not using this object and our DEVs will be flushed with TEST/PROD ModelStore and business DB, so it should remove this error.

But I am still curious how something like this could happen.

Was this reply helpful? Yes No
Verified answer

Kenny Saelen on at

Like (1)

Report

This morning I had a similar case of a map being a table. Meanwhile I solved the issue by updating the ModelElementData table. I went ahead and fetched the Properties field value from a valid environment. Then I update ModelElementData with the correct Properties hex string and restarted the AOS, solved the issue.

Was this reply helpful? Yes No