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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AX 2012 R2 Data Upgrad...
Microsoft Dynamics AX (Archived)

AX 2012 R2 Data Upgrade Issue(s)

(0) ShareShare
ReportReport
Posted on by Andy Adamak 4,074

I've already done all the up front work of upgrading our AX 2012 environment to 2012 R2.  I build up the test server, created the development server, upgraded all the code, imported it to the test server, etc.  Everything works fine on the test server.  Now I am doing a "live test upgrade".  It seemed straight forward.  Install the DB with new model store, upgrade AOS, install client, then run through the Data Upgrade Checklist - much like on the Test server.  The test server seemed to settle down and be happy after a few runs of compile/sync.  My "live test" however, isn't. 

Specifically, the InventJournalTrans and LedgerJournalTrans tables will just not sync, giving me the "Invalid column name 'PARTITIAN' everywhere.  The odd man out error is this:

SQL error description:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type nvarchar to numeric.

Info Synchronize database SQL statement:
INSERT INTO X212X SELECT JOURNALNUM,LINENUM,ACCOUNTTYPE,'',COMPANY,TXT,AMOUNTCURDEBIT,CURRENCYCODE,EXCHRATE,TAXGROUP,CASHDISCPERCENT,QTY,'','','','','',BANKNEGINSTRECIPIENTNAME,ENTERPRISENUMBER,PRICE,'','','','','',SETTLEVOUCHER,POSTINGPROFILE,VOUCHER,AMOUNTCURCREDIT,PAYMENTSTATUS,CASHDISCAMOUNT,ACKNOWLEDGEMENTDATE,VENDTRANSID,CUSTTRANSID,REMAINAMOUNT,TAXCODE,'',0,BANKDEPOSITVOUCHER,PAYMREFERENCE,BANKDEPOSITNUM,PAYMSPEC,PAYMMODE,MARKEDINVOICE,OFFSETACCOUNTTYPE,BANKTRANSTYPE,OFFSETCOMPANY,OFFSETTXT,TAX1099AMOUNT,'',PAYMENTNOTES,MARKEDINVOICERECID,FOREIGNVOUCHER,FOREIGNCOMPANY,TRANSFERRED,CANCEL,NOEDIT,INVISIBLE,TRANSDATE,TRANSACTIONTYPE,DOCUMENTDATE,DOCUMENTNUM,APPROVED,'',PAYMID,FREQVALUE,FREQCODE,DUE,DATECASHDISC,TRANSFERREDBY,LOADINGDATE,FILECREATED,PAYMENTACCOUNT,INVOICE,TRANSFERREDTO,TRANSFER,LASTTRANSFERRED,TRANSFERREDON,PURCHLEDGERPOSTING,BANKCHEQUENUM,PAYMENT,CASHDISCCODE,EXCHRATESECOND,TRIANGULATION,TAXITEMGROUP,VATNUMJOURNAL,LISTCODE,FURTHERPOSTINGTYPE,PURCHIDRANGE,POOLRECID,PREPAYMENT,IMPORTDATE,BANKCENTRALBANKPURPOSECODE,REASONREFRECID,BANKCENTRALBANKPURPOSETEXT,CUSTVENDBANKACCOUNTID,PAYMENTSEQUENCENUM,ERRORCODEPAYMENT,FURTHERPOSTINGRECID,TAXDIRECTIONCONTROL,CUSTVENDNEGINSTPROTESTREASON,BANKREMITTANCETYPE,BANKREMITTANCEFILEID,BANKACCOUNTID,BANKPROMISSORYNOTENUM,'','','','',RELEASEDATECOMMENT,INVOICERELEASEDATE,INVOICERELEASEDATETZID,BANKBILLOFEXCHANGENUM,NEGINSTID,TAXWITHHOLDGROUP,TAX1099STATEAMOUNT,TAX1099STATE,REMITTEECURRENCY,0,'','','','','',DEFAULTDIMENSION,OFFSETDEFAULTDIMENSION,LEDGERDIMENSION,REVERSEENTRY,REVERSEDATE,REVRECID,OFFSETLEDGERDIMENSION,'',BANKRECONCILEACCOUNTATPOST,BANKCHEQUEDEPOSITTRANSREFRECID,MARKEDINVOICECOMPANY,REMITTANCELOCATION,REMITTANCEADDRESS,RELEASEDATE,RELEASEDATETZID,CUSTEINVOICEPAYMDELIVERYNUM,CUSTEINVOICEPAYMSECTIONNUM,CUSTEINVOICEPAYMTRANSNUM,PAYMINSTRUCTION1,PAYMINSTRUCTION2,PAYMINSTRUCTION3,PAYMINSTRUCTION4,VENDINVOICEDECLARATION_IS,TAX1099FIELDS,GSTHSTTAXTYPE_CA,VENDBANKACCOUNT,CUSTBANKACCOUNT,BUDGETSOURCELEDGERENTRYUNPOSTED,OPERATIONTYPE_MX,TAXWITHHOLDITEMGROUPHEADING_TH,APPROVER,EUSALESLIST,0,'','',0,0,0,'','','',TAXWITHHOLDCALCULATE_TH,TAXWITHHOLDALTERNATEVENDORACCT_TH,'',0,0,0,0,0,0,0,'','1900-01-01 00:00:00.000',0,'','','',0,0,'',0,0,'','','',0,0,'','',0,'','','','','',0,0,0,0,'','',0,'',0,0,'','',0,'','',0,0,'','','',0,'','','','','',0,0,0,'','',0,'','',0,'','',0,0,'','',0,0,0,0,0,0,0,0,'',0,0,'','','','','','','','','','','','','','',0,0,0,'1900-01-01 00:00:00.000',0,0,'',0,'','','',0,0,'','','','','','',0,0,'','','',0,CASHDISCBASEDATE,CASHDISCBASEDAYS,SUFFIX,PACKINGSLIP,LEDGERJOURNALDLVDATE_ES,
{fn CONVERT(NAME,SQL_NUMERIC)},
0,'',0,'','',0,'','','','1900-01-01 00:00:00.000','','','','','','','','','','',0,'','','',0,'','','','',dateadd(ms, -datepart(ms,getutcdate()), getutcdate()),DATAAREAID,RECVERSION,5637144576,RECID FROM LEDGERJOURNALTRANS

Error Synchronize database Cannot execute a data definition language command on  (). The SQL database has issued an error.

 

The "NAME" field is an added nvarchar field on the CUS layer with the AX Extended Data Type of 'Name'.  Why on earth is it trying to convert a name to a numeric when I synchronize?  And what is this "X212" table?  I've seen other errors with odd table names like this. 

 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Kevin Kidder Profile Picture
    Kevin Kidder on at

    Typically the cause of this type of error is because you had invalid data for the table in the SQLDICTIONARY table prior to doing the upgrade. The steps to try and correct this would rely on trying to synchronize the table back before the upgrade process, and if it appears to synch with no issues on a test copy of the database and AOS I would try and add a new column and change an index on the table to see if the AOS is able to process that synchronization without error.

    The X212X table comes from the internal Table ID for LedgerJournalTrans (212) and when we modify a unique index we typically push the data to another temp table (X212X) and then recreate the original table and feed the data back in.

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    Kevin,

    Thanks for the information.  I verified that I am able to synchronize the tables before doing the upgrade.  I also added an index & column and was able to sync the changes.  However, after making another attempt at the upgrade I still cannot synchronize without this error.  

    The information you provided on the X212X table makes sense.  When trying to synchronize just the LedgerJournalTrans table, I get a warning about a unique index, and a warning that the table will be dropped and re-added.  I'm using default AX Extended Data Type 'Name' for the added field giving me the error.  I checked the SQL table and it is shown as an nvarchar(60). I checked the SQLDICTIONARY table for table ID 212 and it shows my added 'Name' field with a FIELDTYPE of 0 and STRSIZE of 60.  So all the information says 'string', but it tries to convert it to a number.    

    Is there some metadata messed up somewhere that things this should be a number instead of a string?  How are the temporary tables made? Any more light you could shed on this?  

    Thanks!  

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    In my test upgrade environment I tried deleting all the table customizations on the LedgerJournalTrans table, which included 3 added fields.  I tried again to sync, but got the same error.  I then deleted the 3 correlating records in the SQLDICTIONARY table for the 3 fields I removed and tried to sync again.  

    This time, the table was able to synchronize successfully.  However, all data from the deleted columns is now lost.  I'll have to be sure to backup that table in the live environment before deleting our customizations.  Then, after everything is synced/upgraded, re-add those custom fields and fill in the data from the backup.  So to recap for anyone else, my solution was to:

    1) Backup the problem table into a temporary table.

    2) Delete all customization layers from the table.

    3) In the SQL backend, delete the corresponding dbo.SQLDICTIONARY records for any fields that were removed.

    4) Synchronize the Database in AX and finish the upgrade

    5) Re-add table customizations and synchronize the table.

    6) Import backup data back into the table (custom SQL statement you'll have to write)

    This is what I did, not guaranteeing anyone it's correct for any/all situations, but it seems to have solved my issue.  Still unsure as to why AX wants to convert fields to incorrect values.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    Is the FieldId for the SQLDICTIONARY record for LedgerJournalTrans\Name field correct?  Does it match the AX legacy Id found in the AOT for that field?

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    Brandon,

    I didn't check that... although I found an easier solution.  I had the same issue with a custom table we added, and instead of deleting the whole table I deleted all the table's records in the SQLDICTIONARY, thinking these would be re-generated when I synchronized.  I was right, the SQLDICTIONARY records were regenerated and the table was able to sync without further issues.  

    I wonder if the legacy Id(s) were the issue.  Running the live upgrade this weekend, I'll let you know!

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    Brandon,

    Sure enough... the Legacy Ids do not match.  SQLDICTIONARY table thinks my name field is 60007, but the AOT says 60048.  How do these get out of sync?  I thought that might be the problem once you mentioned it, because the instructions for importing the model store to the test vs. production servers are different in the in-place upgrade instructions.  In the Test server you run a power shell command line to import the model store with the parameter "/idconflict:overwrite", while in the production upgrade you simply select the model store file during the database install.  

    Any advice on how to prevent this, or why/how it happens?  Or is this a bug or oversight?  

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    I'm not sure how it happens.  In theory, of course, it should never happen, but I have seen this kind of mis-match in development environments.  The kernel considers them different fields, even if they have the same name, and wants to drop one and re-create the new one.  If, on the other hand, the field Id matches a completely different field in the AOT, as I suspect yours does, you get other issues.

    You can also fix the SQLDICTIONARY record, and your problem, at least for this one field, will go away, but I would be concerned about how many other mis-matches you have that you don't know about, or worse won't find out about because no SQL errors are being generated and nobody notices until after the upgrade is completed and data has been lost.

    I think you're onto something with the /idconflict parameter.  I thought the production upgrade step used this parameter within the wizard.  In fact I'm almost certain of it or I would have experienced a ton of other issues.

    In my case our 2012 to 2012 R2 suffered from a slightly similar problem.  We had removed the upgrade model from the model store, and the R2 upgrade needs it installed again (with some new scripts, but all of the AX4 and AX5 to AX6 stuff is still in there even though you might be going from 6/2012 to 6.2/2012 R2).  So we had a lot of DEL_ fields in our SQLDICTIONARY table that no longer had any AOT references for the upgrade model to match up, so it all went in with different Id's and we had to fix our SQLDICTIONARY table manually.

    I think I still have some SQL somewhere that at least identifies mis-matches.

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    I'm also concerned with other non-identified issues that could cause future headaches.  Do you have that bit of SQL you could share?  I'd be interested to see how many mismatches I have.

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    I tried to attach a file with some SQL in it.  I used this to identify mis-matches between SQLDICTIONARY and the model store.

    You have to change the database references as appropriate.  SQLDICTIONARY is in your business data database, and the ModelElement and related tables are in the model database.

    This is provided for academic purposes only.  Use at your own risk.  Any changes to any Dynamics AX database outside of the user interface is unsupported.

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    Thanks for that! It picked up one more I went and fixed right away.  Marked your reply as the answer as fixing the SQLDICTIONARY.FIELDIDs to match your legacy IDs will get rid of these kinds of errors.

    Thanks for all your help!

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans