SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

This question has suggested answer(s)

Have tried to do the In-place upgrade on a Ax 2012 CU3 install with 3 additional KB's following the guidelines from here:
 http://technet.microsoft.com/en-us/library/jj733502.aspx

For the step "Import ISV-provided models into the development system", there are no isv layer changes but I do have a var layer model with my changes that was exported on a clean R2 install. I am treating this the same as if I were importing an isv layer change here. The var layer model has all the same changes for the existing var layer updated to R2. When I open Ax at this point to follow the "Data upgrade checklist for in-place upgrade" I am getting the following error:

Cannot select a record in Alerts - event inbox (EventInbox).The SQL database has issued an error.
SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'PARTITION'.
SQL statement: SELECT T1.INBOXID,T1.COMPANYID,T1.RULEID,T1.ALERTTABLEID,T1.ALERTCREATEDDATETIME,T1.ALERTCREATEDDATETIMETZID,T1.PARENTTABLEID,T1.USERID,T1.ISREAD,T1.SUBJECT,T1.ALERTEDFOR,T1.VISIBLE,T1.DELETED,T1.SENDEMAIL,T1.ALERTFIELDLABEL,T1.ALERTFIELDID,T1.TYPEID,T1.TYPETRIGGER,T1.SHOWPOPUP,T1.EMAILID,T1.EMAILRECIPIENT,T1.ALERTINSTANCERELATIONTYPE,T1.NOTIFICATIONSOURCE,T1.EPDRILLDOWN,T1.ISAGGREGATED,T1.NOTIFICATIONTYPE,T1.DUEDATETIME,T1.EMAILTEMPLATEID,T1.GLOBALRULE,T1.ENUMTYPE,T1.EXTENDEDDATATYPE,T1.CREATEDDATETIME,T1.DEL_CREATEDTIME,T1.RECVERSION,T1.PARTITION,T1.RECID,T1.MESSAGE,T1.KEYFIELDNAMELIST,T1.KEYFIELDNAMEDATA FROM EVENTINBOX T1 WHERE ((PARTITION=?) AND (((VISIBLE=?) AND (USERID=?)) AND (DELETED=?)))

I can't proceed past this point. What do I do next?

 

All Replies
  • Before you import your VAR layer model, you should allow the AX client to open and launch into the code upgrade checklist so that it can perform all of the steps listed under the Upgrade preparation section of the checklist, which includes a Synchronize of the dictionary. You should follow through the upgrade guide instructions except that instead of doing a code upgrade project you can exit out and import your model at that point.

  • Kevin is right. And also; you can ignore that error around EventInbox. It is not critical and will be gone after you have synced that table.

    Good luck down the in-place upgrade path.

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

  • Working on Kevin's suggestion...

  • No change. Same results. Little bit more info though.

    The 'PARTITION' error occurs after I select the checklist and click OK so it happens when the Sys objects are being compiled to run the checklist not when I open the client.

    Also, since this is an upgrade there is a v6.0 var layer model that is present before I get to the upgrade perparation step. Should I have deleted that before I ran the R2 setup? Should I have deleted it after the exportmodelstore step but before runing the upgarde prepartaion portion of the in-line code upgrade checklist? Should I delete it at all?

  • As poster skaue said previously, the EventInbox errors can be ignored and they shouldn't prevent you from doing any of the checklist options - they will go away once you have gotten to synchronize successfully.

    With the VAR layer in your description, if that is the layer you are working on importing into your system, then the 6.0 VAR layer should still be present in your dictionary when you import your 6.2 VAR model so that your element IDs remain the same for any existing model elements. The only layers that you would delete would be ones that are higher than the layer you are currently working on, such as CUS and USR if you are working on the VAR layer. Then when you move up to the next CUS layer, you would be deleting the USR layer if you have one.

    Kevin

  • I tried to run through the upgrade procedures on an install that was v6.0 RTM, CU3, 3 additionall KB's and no customizations. Got the expected 'PARTITION' error. I tried to compile from there and got the 'PARTITION' error on numerous objects. I was unable to get the compile, CIL or Sync to complete successfully. Additionally when I got to the Sync, it stopped with an error that the internal size for VendInvoiceDocumentTmp record was too large.

  • When you run the upgrade of PROD, make sure you upgrade both the database, AOS and client to R2 in one operation. The next thing you do is do the kernel compile. The guide does not say so, but without the kernel compile at this point, it just won't work. You also need to make sure batch jobs are set to withhold (status = 0) and batch configurations needs to be set to the correct AOS instance. You need these to be ok before you initiate the data upgrade jobs.

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

  • So the upgrade doc/"How to" doesn't seem to be ready for prime time.

    Three issues I have run into that are not mentioned in the "How-to" upgrade doc but were known to MS support.

    1.   The previously mentioned 'PARTITION' SQL error.

    Running a compile and sync does make this go away but if you are pretty much guaranteed to see it or there is high likelihood of seeing it, it should be noted in the "How to".

    2.   When running the sync for the first time (as required to make the 'PARTITION' SQL error cleared up) you will get the error:

        "The internal size of a VendInvoiceDocumentTmp record is 25424 bytes, but Microsoft Dynamics is by default performance-tuned not to exceed 24576 bytes.

        It is strongly recommended that you split your table(s) into smaller units.

        Alternatively, you have to specify a 'Maximum buffer size' value of 25 Kbytes or higher on the 'Database Tuning' tab page in the Microsoft Dynamics AX Server Configuration Utility. The default value is 24 Kbytes.

        Exit Microsoft Dynamics immediately, and follow the directives above. Use of the table(s) will cause unpredictable results."

    The MS support said they see the same thing on their demo data and that it can be ignored. Before getting a response from MS I had already followed the instructions and increased to buffer size and the error went away. Additionally on a previous upgrade attempt I had seen this error. So I created a new profile with a larger buffer before starting the next upgrade attempt but I still got the same error. Since I got the error either way I have since stopped using a profile with the larger buffer.

    Additionally, once the sync is complete there will be bunch of warnings similar to:

         Cannot execute a data definition language command on Transactions (RetailTransactionTableView).

         The view has been disabled. Configuration key on following table(s) is off

    Again, known, evidently can be ignored and not in the "How-to".

    3.   Cannot get the compile task in the Upgrade Preparation" portion of the "Code upgrade checklist for in-place upgrade" checklist to complete successfully.

    From MS Support:

        If the checklist is coming up, then there is the known issue with running compile in the AX client where it does not display compile errors.

        If you run the compile from the dev workspace you can see the errors. If there are no errors go back to the checklist and continue.

    It is a known issue (also not in the "How to") that when you run the Compile from the checklist that errors are not displayed so you need to run a compile from the AOT before running it from the checklist.

    This is currently where I am at. I've run the AOT compile and still don't see compilation errors. I do have 14 warnings on objects that I do not modify. This is the same result I see when I run the compile from the checklist. Are those what is causing the Compile task to not complete successfully? I don't know, still working on it. The warnings I am seeing are all:

        Recursive local functions are not supported in CIL from X++. Consider removing them.

  • You are in the code upgrade, as far as I can tell. Am I correct?

    If you install a new AOS for R2, the buffer size will default to 48, as far as I know. This is also the new R2 recommended setting.

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

  • Yes, still in code upgrade.

    48 would probably explain why I still got the buffer error when I changed the profile before upgrading as I only set it to 30. Thanks.

    Again this is something I would expect to be atleast noted in the "How to" doc.

  • Curt - yes we definitely have some additional details that we are now live on TechNet in a revised version of the in-place Upgrade Guidelines - http://technet.microsoft.com/EN-US/library/jj733502(v=ax.60).aspx These changes will be rolled into a future release of the larger upgrade guide as well. For your Compile "not finishing" statement - with the in-place upgrade checklists, the Compile option will not mark itself automatically when the compile finishes. We are going to change that in a future rollup for the Compile on the top section of the Code Upgrade checklist, but the Compile option in the per-layer section will not be marked automatically since that is a repetitive process. If you know that your compile was successful (with just warnings as you mention above) you should be able to mark the option finished and continue with the checklist.

    Kevin

  • Kevin, Could you please (pretty please) give us a heads up on whether or not the production cut-over should work? Right now, I am still unable to successfully data upgrade production based on a R2 prepared modelstore (based on a modelstore from RTM production and successfully code+data upgraded in test).

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

  • Since you asked so nicely, here is a list of steps that we have used which has worked for the final production data upgrade setup steps. These steps will be reflected in the updated guide with the small exception that steps 1,3 and 4 below will be executed earlier during the code upgrade process. They are required if you already have a fully prepared R2 modelstore already made.

    Start of by doing an un-install all AX 2012 6.0/6.1 components except the AOS using add/remove programs:

    1. Get a copy of AX2012 R2 version of AXUTIL.EXE and have it available for step 3 below

    2. With the AX 2012 6.0 AOS still installed, run the AX2012 R2 setup routine and choose Database as the only option on the setup window. Choose to Configure an existing database and pick your existing server and database, and then choose to import your upgraded R2 model store when you come to that section of the setup wizard.

    3. After the database setup finishes, open a command prompt where your AXUTIL.EXE is, and type in the command (this step assumes your production database is your only AOS instance)

        axutil set /installmode

    4. You should get a message for the _model database “The InstallMode has been successfully applied” – this will guarantee that the model store knows that it is in the mode that needs to do the extra steps required for creating the stored procs and synchronizing the system tables.

    5. Then Uninstall the AX 2012 6.0 AOS

    6. Then install the AX2012 R2 AOS only and wait for the AOS to restart

    7. Then install the client/debugger/Management Utilities

    Uninstalling the 6.0 AOS between steps 5 and 6 make sure that all of the new configuration settings and changes we made with AX 2012 R2 get setup properly - such as increasing the buffer size to 48, adding a registry key to tell the AOS to use the _model database for the code, etc.

    If these steps aren't working, then I would ask that you log a support request with a description of the errors and attaching the setup logs from each of the setup runs described above.

    Kevin

  • Very, very interesting, Kevin, and thank you for answering! Now the hard choice is between testing this *today* or honor the fact that it is Valentines Day and try spend time with your loved one instead of geeking out.

    A couple of comments here:

    Having the 6.0 AOS installed while doing step 3 ensures that the correct database gets marked for installmode with that command. The reason reason you want me to use the R2-version of AXUTIL is the fact that it will look for the "_model"-database instead of the original database. Does this mean I could also simply use the db and server parameter instead? Reason I'm asking is to find out if having the 6.0 AOS installed is crucial or not.

    I've noticed that when setting up the TEST for the first time, you need to run a kernelcompileall before you prepare DEV for code upgrade. How come you don't need to do the kernelcompileall in PROD? You would think the R2 modelstore prepared and tested is already compiled and ready, but while strugling with this, I started wondering if there were any dependencies that would not be ok unless the kernelcompileall-step was done in PROD as well. Is kernelcompileall something that can be skipped in PROD?

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

  • The axutil set command itself could use the /db and /server parameters (you would need to pass in the _model database name), but the real reason for having the 6.0/6.1 AOS installed is that in most cases we have found that the Setup utility will not give you the option to select an existing model store for importing if the 6.0 AOS is not installed when you are configuring databases in step 2 of the previous list.

    For your second question - the kernelcompileall command is not required for the PROD upgrade because of the use of the fully compiled model store.

    Kevin