You’re offline. This is a read only version of the page.
Hi all,
We have a customer where a model was installed previously. Instead of updating the model, the old model was deleted and the new version installed. That caused some DB sync errors on creating indexes. This is happening on AX2012R2 with CU7 and various kernel builds, including the latest.
The error is:
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]Column name 'PARTITION#34' does not exist in the target table or view.
SQL statement: CREATE INDEX I_106718BATCHIDX ON "DBO".TABLENAME (PARTITION,RELATIONTYPE,PARTITION#34) WHERE INSTANCERELATIONTYPE IN ( 106718 )
Indeed the field Partition#34 was not defined. When looking at the table in the SQL database, it appears that almost all columns are wrongly define. They do have fields like Partition#34,Partition#35 and DataAreaID#21,DataAreaId#22. Also when we look at the table SQLDictionary, we see fields like Description created with SQL name Partition#34.
The field types used for the table columns in SQL are also incorrect.
There are various environments where only one environment does have this issue. As there is some important data and also the latest developments in this environment, we cannot simply refresh it from the Production or another Test instance.
Does anyone encounter this same issue before and found a solution to have the correct columns again in the SQL database and the SQLdictionary table?
*This post is locked for comments
Hi Sohaib,
The query which fails is stated in the original post above. It is a model with an ISV solution which is installing fine in every other environment. Only one environment fails. Also if we create a new environment based on the database and modelstore where it goes wrong, it fails. So, I do think there is some reference in the database wrong.
Hi André,
Is it showing the failed query in Dynamics AX SQL synchronization log..?
the fields like Partition#34,Partition#35 are created usually when multiple tables are joined in views. as the each table has these system fields so sql server combines these with a number.
Secondly, is it a customized table or standard ax tables..?
If you can email me the xpo for the table only i can try to import it into one or more environment, to see how it behaves in my VM on import and synch.
I forgot to mention that this only happens for two tables which are part of table inheritance. Other tables are synchronized correctly.
Hi Sohaib,
Thanks for your input. The tables related to this model are empty, we cannot lose other data. We also dropped the tables and deleted related records from the SQLdictionary table. We also fully removed the model, restarted AOS and re-installed it multiple times. In your blog, you are also referring to the SQLSyncInfo table. This table is empty here.
Unfortunately, your blog (however valuable) is not solving this problem.
Hi André,
I understand that saving data is important for you and this disallows you from dropping schema of the table.
Have you had opportunity to follow something as this
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,219
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (