web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL Error when doing A...
Microsoft Dynamics AX (Archived)

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

(0) ShareShare
ReportReport
Posted on by Curt Weaver 1,310

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?

 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Kevin Kidder Profile Picture
    Kevin Kidder on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

  • Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

  • Curt Weaver Profile Picture
    Curt Weaver 1,310 on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    Working on Kevin's suggestion...

  • Curt Weaver Profile Picture
    Curt Weaver 1,310 on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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?

  • Kevin Kidder Profile Picture
    Kevin Kidder on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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

  • Curt Weaver Profile Picture
    Curt Weaver 1,310 on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

  • Suggested answer
    Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

  • Curt Weaver Profile Picture
    Curt Weaver 1,310 on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

  • Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

  • Curt Weaver Profile Picture
    Curt Weaver 1,310 on at
    Re: SQL Error when doing Ax 2012 --> Ax 2012 R2 In-Place Upgrade

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

#2
NNaumenko Profile Picture

NNaumenko 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans