AX 4 -> 2009 upgrade errors

Hello,

When trying to synchronize in the upgrade checklist i have 4 errors.
Dynamics AX is trying to drop a column that is used as a primary key.

SQL error description: [Microsoft][SQL Native Client][SQL Server]The object
'I_2897HIERARCHYIDISEQNUMDX' dépends on colum 'HIERARCHYID'.
SQL statement: ALTER TABLE DIMENSIONCOLLECTION DROP COLUMN DIMENSIONVALUE,
HIERARCHYID, ID, SEQUENCENUMBER

SQL error description: [Microsoft][SQL Native Client][SQL Server]The object
'I_2898HIERARCHYSEQNUMIDX' dépends on column 'HIERARCHYID'.
SQL statement: ALTER TABLE DIMENSIONHIERARCHYCOMBINATION DROP COLUMN
DIMENSIONCOMBINATION, DIMENSIONCOMBINATION2_, DIMENSIONCOMBINATION3_,
DIMENSIONCOMBINATION4_, DIMENSIONCOMBINATION5_, DIMENSIONCOMBINATION6_,
DIMENSIONCOMBINATION7_, DIMENSIONCOMBINATION8_, DIMENSIONCOMBINATION9_,
DIMENSIONCOMBINATION10_, HIERARCHYID, PARENTRECID, PARENTRECID2_,
PARENTRECID3_, PARENTRECID4_, PARENTRECID5_, PARENTRECID6_, PARENTRECID7_,
PARENTRECID8_, PARENTRECID9_, PARENTRECID10_, SEQUENCENUMBER,
EFFECTIVEPARENTRECID

SQL error description: [Microsoft][SQL Native Client][SQL Server]La objet
'I_2899HIERARCHYDIMIDX' dépend de colonne 'DIMENSION'.
SQL statement: ALTER TABLE DIMENSIONSETCOMBINATIONDUP2899 DROP COLUMN
DIMENSION, DIMENSION2_, DIMENSION3_, HIERARCHYID, MINRECID

SQL error description: [Microsoft][SQL Native Client][SQL Server]La objet
'I_2900SETHIERARCHYIDX' dépend de colonne 'SETID'.
SQL statement: ALTER TABLE DIMENSIONSETCOMBINATIONTEMP DROP COLUMN SETID,
HIERARCHYID, DIMENSION, DIMENSION2_, DIMENSION3_, ACTIVE,
PARENTCOMBINATIONSEQNUM, PARENTRECID




--
Regards,

Jérôme Piquot

Gédimat Wolff - Catenne
6, rue de la Gare
67210 Ernolsheim sur Bruche
FRANCE

I´m facing the exact same issue.Have you found any solution on this?

I´m facing the exact same issue.
Have you found any solution on this?

Sincerely

Richard Hellstenius
richard.hellstenius@accigo.se

Hi Richard,Yes, I made the changes manually.

Hi Richard,

Yes, I made the changes manually. Generate the DDL file and see the 3 blocks
of sql, make changes in the table with SQL management studio. Do not run the
sql code just see what he wants to do on the table and change it by clicking
on the 'Design' option of the table in the object explorer.

Then run the code in the ddl block than starts with a delete in the
dictionary table to the end of block.

Do this for the 3 blocks.

I do not know if this is the good way but it seems to work.

Regards

Jérôme Piquot

Gédimat Wolff - Catenne
6, rue de la Gare
67210 Ernolsheim sur Bruche
FRANCE

Hello Jérôme,After discussing this with a senior colleague, he gave me the

Hello Jérôme,

After discussing this with a senior colleague, he gave me the following
instructions/pointers:

Before synchronize the database, open the AOT and accessing the four
mentioned tables and disable the mentioned indexes (the ones found in the
Event Viewer). To do this correct, I had to make the changes to all tables
before saving.

After that, I then had four usr-modifications in the AOT. I then run the
Synchronize step in the Upgrade checklist and everything worked fine.

After that I deleted the usr-modification from the AOT (setting it back to
original state). I then executed the next step in the upgrade checklist -
Postsynchronize.

That also went well.

The reason to why the errors occured was/is because the related tables are
moved down to the SYS layer (previously in the SYP-Layer) and the id´s has
been altered. Microsoft has forgotten to create a pre-sync job that handles
this in the upgrade.

Sincerely

Richard Hellstenius
richard.hellstenius@accigo.se
www.accigo.se

Hi Richard,Thanks for this info.

Hi Richard,

Thanks for this info. I will try this too, it seems to be a better way than
what i was doing.

--
Regards,

Jérôme Piquot

Gédimat Wolff - Catenne
6, rue de la Gare
67210 Ernolsheim sur Bruche
FRANCE

-- This posting is provided "AS IS" with no warranties, and confers no rights.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

This issue is documented under Upgrade Guide (Troubleshooting section - page

This issue is documented under Upgrade Guide (Troubleshooting section - page
105-106). Please refer to suggested work around.
http://www.microsoft.com/downloads/details.aspx?FamilyId=D9DAE144-55B4-432D-B11E-F65DDB2D1B9C&displaylang=en

I'm providing the reference below:

thanks - Deepak
Synchronization fails during upgrade Issue: If you use SQL Server, and you
are upgrading from Microsoft Dynamics AX 4.0 SP1 with an application build
equal to or greater than 4.0.2500.61 or from Microsoft Dynamics AX 4.0 SP2,
an error can occur during synchronization. In this case, the four tables
listed below will fail and you will be unable to continue with the upgrade.
??DimensionCollection ??DimensionHierarchyCombination
??DimensionSetCombinationDuplicate ??DimensionSetCombinationTemp Possible
cause: The field IDs for the four tables listed above are different in
Microsoft Dynamics AX 4.0 SP2 from Microsoft Dynamics AX 2009. However, the
table IDs are the same in both releases, which causes the upgrade scripts to
fail to start the upgrade code process to correct the field IDs. Because the
field IDs are different in the database and the AOT, the fields are dropped
and recreated during the synchronization step. The synchronization step
fails because there are SQL indexes that reference the fields and prevent
their removal. Solution: Before you upgrade, you must execute the following
SQL script on your Microsoft Dynamics AX 4.0 SP2 database.

--DIMENSIONCOLLECTION

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50002 AND TABLEID=2897;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50005 AND TABLEID=2897;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50007 AND TABLEID=2897;

UPDATE SQLDICTIONARY SET FIELDID=4 WHERE FIELDID=50008 AND TABLEID=2897;

--DIMENSIONHIERARCHYCOMBINATION

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50002 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50003 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50005 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=4 WHERE FIELDID=50006 AND TABLEID=2898;

UPDATE SQLDICTIONARY SET FIELDID=5 WHERE FIELDID=50009 AND TABLEID=2898;

--DIMENSIONSETCOMBINATIONDUP2899

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50001 AND TABLEID=2899;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50002 AND TABLEID=2899;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50003 AND TABLEID=2899;

--DIMENSIONSETCOMBINATIONTEMP

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50001 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50002 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50003 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=4 WHERE FIELDID=50004 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=5 WHERE FIELDID=50005 AND TABLEID=2900;

UPDATE SQLDICTIONARY SET FIELDID=6 WHERE FIELDID=50006 AND TABLEID=2900;

--PROVISIONALHIERARCHY

UPDATE SQLDICTIONARY SET FIELDID=1 WHERE FIELDID=50001 AND TABLEID=2901;

UPDATE SQLDICTIONARY SET FIELDID=2 WHERE FIELDID=50002 AND TABLEID=2901;

UPDATE SQLDICTIONARY SET FIELDID=3 WHERE FIELDID=50007 AND TABLEID=2901;

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Dropping Tables while synchronization in Ax 4.0 Sp2

Hi,

I restored the live database into the development database, and when I try to synchronize the database in the Development client , tables are getting dropped. Please help in this case that how can I avoid tables getting dropped.

With Thanks,

Sanjeev





Re: AX 4 -> 2009 upgrade errors
20-Jun-08

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


EggHeadCafe - Software Developer Portal of Choice
Web access to EnterpriseDB with Data Access Pages
http://www.eggheadcafe.com/tutorials/aspnet/3d962921-a3ce-41b7-b85e-f3e625e35771/web-access-to-enterprised.aspx