Reducing EDT string size.

(0) Share

Report

Posted on by KCDeVoe

1,450

Somehow in our Development environment the EDT SysGroup got changed to String20. Needless to say it has affected everything...

I've deleted the CUS layer and attempted to run a Sync to fix the various places that this affected. However, the sync fails with messages such as:

"SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot DROP TABLE 'DBO.DIMENSIONATTRIBUTEVALUECOMBINATION' because it is being referenced by object 'AX_GL_Financial_View'."

What are our options for fixing this? Is a data restore from our Test or Production environment the only way? I would like to avoid this because it creates other areas of frustration as it overwrites all of our TFS settings.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Bilal Issa 4,370 on at

Like (0)

Report
Copy link

Link copied!

Hi,

you will need create a new user group to replace 'AX_GL_Financial_View' and in general ledger -> ledger , you need to change the user group that allow to post to the new group.

and then delete the 'AX_GL_Financial_View', and try to Sync again.

Regards,

Bilal

Was this reply helpful? Yes No
KCDeVoe 1,450 on at

Like (0)

Report
Copy link

Link copied!

Thanks Bilal, but I don't believe that's the issue.

The issue seems to be it wants to drop and re-create DimensionAttributeValueCombination in order to reduce the columns that are extended from SysGroup. It's not dropping because it's referenced by the above view.

I don't believe it would be a good idea to drop and re-create the table as everything in it would be lost. So I suppose it's a good thing that it errored.

Was this reply helpful? Yes No
André Arnaud de Cal... 305,465 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Kevin,

Indeed. Dropping the table is incorrect. Can you check if some license keys have been disabled? It looks like AX thinks the table is different. Maybe environment specific element ID's are different now. Have you synchronized this database before without problems on this environment?

Was this reply helpful? Yes No
KCDeVoe 1,450 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the reply, André.

I don't believe it's element IDs. It appears all related to the Extended Data Type 'SysGroup'. Somehow recently this EDT was changed to String20. From what I can gather that had to have been accidental and not related to any development. So everything that is extended off of it was also increased to a String20. Then when a sync was completed it expanded all of these columns in SQL to a nvarchar(20). Now, today, when I was moving something into our Test environment I noticed the changes to the columns size on a table and investigated. Upon investigation I discovered that SysGroup was set to String20. After looking into why it was changed we came to the conclusion that it had to have been accidental.

After this we deleted the CUS layer for SysGroup, returning it to a string length of 10. This would then need to propagate through down to the tables and reduce the column sizes back to 10. Which brings us to our problem. The table DimensionAttributeValueCombination has a field DEL_AccountNum with EDT LedgerAccount. LedgerAccount is extended from SysGroup and so it is trying to reduce to nvarchar(10) and SQL is throwing the following:

"Cannot execute a data definition language command on ().

The SQL database has issued an error."

"SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot DROP TABLE 'DBO.DIMENSIONATTRIBUTEVALUECOMBINATION' because it is being referenced by object 'AX_GL_Financial_View'."

"SQL statement: DROP TABLE "DBO".DIMENSIONATTRIBUTEVALUECOMBINATION"

"Problems during SQL data dictionary synchronization.

The operation failed."

Was this reply helpful? Yes No
André Arnaud de Cal... 305,465 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Kevin,

I really wonder why AX thinks it should drop the table.

If it is not your production environment you can try to find a backup of the database before the changes. Before restoring, also backup your current database, in case the restore is not working at all.

Was this reply helpful? Yes No
KCDeVoe 1,450 on at

Like (0)

Report
Copy link

Link copied!

That could be the solution. Our DBA is out for the night so I will have to check with him in the morning to see if we have a proper backup.

I will update when I know more.

Thanks again!

Was this reply helpful? Yes No
Verified answer

KCDeVoe 1,450 on at

Like (2)

Report
Copy link

Link copied!
Update on the problem. Also, other problems we ran into and solutions for those that may come across this post in the future.

1) The view turned out to be a custom view written outside of the system for data migration. I was able to script it and delete it. The sync then only threw 6 errors.

2) The other errors were all in the form: "The database reported (session 9 (<userId>)): [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.. The SQL statement was: 'INSERT INTO X2303X SELECT ...". My first thought was ID conflicts in the SQL Dictionary table. This turned out to NOT be the case. I'm still not sure what it was attempting to do that was causing this, but here's how I handled it:

For tables with the error that had no records. Dropped the table in SQL and Sync'ed from the AOT.

1 table had a single record in it. I scripted the record. Dropped the table. Sync'ed from AOT. Then ran the scripted entry to reinsert.

Then came DirPartyTable. I couldn't simply drop and re-sync due to the amount of records and inter-connectivity with other tables. After some trial and error this is what I did: 1) Script DirPartyTable from SQL and create DirPartyTable2. There's an ID partway down that relates to the TableId that will need a temporary value of whatever you want (that's not a duplicate). 2) Insert Into DirPartyTable2 all of the records from DirPartyTable. 3) Drop DirPartyTable. 4) Sync from AOT. It wouldn't let me insert records back into DirPartyTable from DirPartyTable2. So I did the following. 5) Drop the new DirPartyTable created from sync. 6) Rename DirPartyTable2 to DirPartyTable. 7) Script new table from DirPartyTable to create DirPartyTable2 making sure to change the ID partway down the script to the original table ID. 8) Insert into DirPartyTable2 all the records from DirPartyTable. 9) Drop DirPartyTable. 10) Rename DirPartyTable2 to DirPartyTable. 11) Sync from the AOT.

Somehow the 'ol' switcheroo does the job and the sync completes and all records that had been changed to 20 return to a nvarchar(10) with no lost data (unless you put more than 10 characters in one of the columns).

Thanks everyone for the helpful tips!

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi,

You can also export the data of the table into a directory using "Test Data Transfer tool" (The table which needs to be dropped in SQL).

1. Delete the records in "SQLDictionary" for that tableId (table Id can be found in AX table properties ) in the AX database.

2. Drop the Table in SQL.

3. Right click the table in AOT and click Synchronize.

4. Stop AOS and import back the records using Test data transfer tool.

thanks,

Manish

Was this reply helpful? Yes No