SQL Error when doing Ax 2012 CU3 TO Ax 2012 R2 In-Place Upgrade

This question has suggested answer(s)

Hi,

I still have problems getting the CU3 to R2 data upgrade. Below are the steps which we followed

We already have code upgrade done from CU3 to R2. We only have to do data upgrade from CU3 to R2.

Below are the steps we followed

AX2012 CU3 database

1. Deleted all Foundation upgrade objects and then synchronized the database. No syc errors.

2. No modifications to the core system classes (Application, Global, Info, Session)

3. None of batch jobs are in Waiting status

4. Took the DB backup

AX2012 R2 machine

1. Have restored the above CU3 database and provided full access to the current user to the DB

2. Client, debugger and AOS are already installed

3. Uninstalled the existing R2 AOS

4. Machine have enough space and 8 GB RAM

5. Have Exported the model store from R2 model database for already upgraded code from CU3 to R2 (this backup is taken from a different machine)

6. Run the AX2012 R2 setup and choose Database as the only option on the setup window. Choose to Configure an existing database and picked the

existing server and database(CU3 DB), and then choose to import the upgraded R2 model store(exported above).

7. After the database setup finishes, we saw model DB created successfully without any errors

8. Opend a command prompt where your AXUTIL.EXE is, and type in the command

axutil set /installmode /s:[ServerName] /db:[DB_Model]

9. Then installed the AX2012 R2 AOS only connecting to the above DB(data and model) and waited for the AOS to restart.

10. After AOS restarted, when trying to connect to AX getting lot of errors with sync issues as below

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'PARTITION'.

SQL statement: SELECT

There is also an error which says

User 'Syeds' is not authorized to delete a record in table 'SYSCONFIG'. Request denied.

Cannot edit a record in Configuration keys (SysConfig).
Access Denied: You do not have sufficient authorization to modify data in database.

Current user is a sys admin on the machine and also have full access to both the above DB's.

Please let me know if i am missing something.

Regards,

Syed

All Replies
  • Has anyone facing above issue?

  • Hi Syed,

    I don't have exp on this but you can try refer to this post:

    community.dynamics.com/.../182261.aspx

    "Partition" is new in AX2012R2 and most, if not all, tables in AX now have a partition column (much like dataAreadId column). That's where the error comes from.

    My blog | PBC

    This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

  • I would not worry to much if you receive a couple of errors due to the missing PARTITION column, but if you several hundreds of them, then there might be a more severe issue.

    I never did the in-place upgrade as anyone else than "admin", so I'm not sure if there are any dependencies there. Having sufficient access on the server and the database does not grant full access within AX. You must also make sure your Active Directory User has System Administrator role within AX.

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • Hi Skaue,

    Yes current user is a system admin user in AX.

    I wouldn't have mind if it was only Sync errors, but my real problem is after getting the sync errors while connecting to AX, i am not able to connect to the workspace. It just shows AX workspace without any module visible in it. I couldn't even connect to AOT.

    Regards,

    Syed

  • It worries me that you can't even open the Developer Workspace and open the AOT.

    Try stop the AOS, connect to the database and replace the 'Admin* user with your own. You need to replace the SID, NetworkDomain and NetworkAlias. Read more about that here: community.dynamics.com/.../easy-way-to-get-sid.aspx

    If you need help to find your own SID, please ask.

    Start the AOS again and see if you still lack access to the AOT.

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • The same admin user is part of both the databases, so SID is same. I tried restarting the service without any help.

    Regards,

    Syed

  • Syed - your steps vary a little bit from the newly published recommended procedure for how to do this database upgrade step of the install from what I am reading.

    The machine where you are going to run the R2 setup program on it should have no R2 components on it when you start this process, and our recommendation is that you would only have the AX 2012 6.0/6/1 AOS installed on the machine pointed at your CU3 database. Here would be my recommendation for the steps to follow which can also be found here in more detail  (technet.microsoft.com/.../jj733502.aspx)

    These steps refer only to the machine where you are going to run the R2 setup and assume that you have copied in the database and provided access to the database for yourself and the AOS service account you are using.

    1. Uninstall all AX 2012 R2 components if any are installed. Uninstall any AX 2012 RTM/Feature Pack components EXCEPT for the CU-3 version of the AOS which is setup to point at the CU-3 database you have setup.

    2. Run the AX 2012 R2 setup and select only the Database as the only option to install. Then follow the wizard and do the same steps you listed - Configure Existing Databases, choose your current CU-3 database, and select the AXModelStore file that you created when given the option. After the setup completes, you should see just as you did before that the model database got created.

    3. Copy in from a different machine an AX 2012 R2 version of the AXUTIL.EXE file and run the command line that you did above to set the install mode:

          axutil set /installmode /server:<servername> /db:<dbname_model>  

    4. Run the AX2012 R2 setup again and only choose the AOS and if prompted specify that you are upgrading an existing AOS. Then choose your Database name and leave the Baseline database blank. Wait for setup to continue and for the AOS to restart.  At this point, if you look in the SQL database you should see that a table SYSSETUPPARTITIONLOG got created. If you don't see that table, then something has gone wrong with steps 3 and 4. At this point, my recommendation would be to uninstall the AOS, and rerun step 3 and 4 - if at all possible this second time, install the AOS on the actual SQL Server box to make sure that there aren't any issues with being able to set permissions across the two servers.

    5. Then install the client and other components and launch the client - if the checklist doesn't come up, attempt to go through the development workspace to launch the checklist. If you still continue to get errors other than a few PARTITION errors on tables that are different from SYSSETUPPARTITIONLOG, then you will need to open up a support request so we can try and dig further into your environment to get to the bottom of the issue.

    Kevin

  • Thanks Kevin for the steps. I will try these setps and let you know the results.

    Regards,

    Syed

  • Hi Kevin,

    I did followed the exact steps as explained, but still i am getting the same errors. What is the difference i see is after step 4, table SYSSETUPPARTITIONLOG is reflecting in ModelElement table of model database whereas the same is not shown in the actual AX database(CU3).

    I have all AX2009 components installed in my machine. is there a problem having AX2009 components for in-place upgrade?

    Regards,

    Syed

  • Syed - sorry that didn't work out for you - having AX 2009 components shouldn't matter in any way, we just can't have AX 2012 RTM and AX 2012 R2 components installed at the same time. The SysSetupPartiionLog table is a new one for R2, but my original question was meant to ask if you see it get created in the actual database during the AOS restart. The only other suggestion that I can offer without digging in through a support case would be if you are still receiving permission errors, you could attempt to use AXUtil to grant permissions prior to restarting the AOS.

    The command to grant permissions would be:

       axutil grant /aosaccount:ACCOUNTNAME /db:DBNAME /s:SERVERNAME

    and you could run that against both the original database and the _model database.

    If that doesn't work for you, I'll have to ask that you open a support incident and include your setup logs and these details when opening the case.

    Kevin

  • Hi Kevin,

            With regards to Step 3 in your previous post.

    3. Copy in from a different machine an AX 2012 R2 version of the AXUTIL.EXE file and run the command line that you did above to set the install mode:

          axutil set /installmode /server:<servername> /db:<dbname_model>  

    Is it ok if the AXUtil.exe command is run from another machine which has the AX2012 R2 management tools installed on it? I would imagine that you can set the installmode on the modelstore from anywhere so long as you specify the correct server and database.

    -Albert

  • Yes Albert you are correct that you could use an R2 version from another machine using the syntax you specified.

    Kevin

  • Just make sure you set installmode on the correct modelstore. When upgrading from RTM there is a modelstore inside the business database. You want to make sure you set the installmode on the *model database*.

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • Hello All,

    Have anybody tried to check the license configuration when model store was exported and configuration when upgrade is performed?

    I assume this could be the reason. Because all the errors are only because of missynchronization  between DB, SQLDictionary table and AX 2012 R2 Data Dictionary.

  • In the same AX upgrade scenario I get these SQL errors when performing an DB sync as preparation to the actual DATA upgrade:

    Object Server 01:  The database reported (session 4 (Admin)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'PARTITION'.. The SQL statement was: "CREATE VIEW "DBO".SALESLINEVIEW_IN... and others.

    Any help greatly appreciated.