Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015 Part 3
This is the third part of the series of “Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015”
The previous articles can be found here:
Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015
Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015 Part 2
This time we would like to talk about a message which could occur during the upgrade to Dynamics NAV 2017:
“UPG Item does not exist”
This error shows up like this:
SessionId: xx CodeunitId: 104xxx (depending on the version) FunctionName: UpdateItem CompanyName: CRONUSxxxx StartTime: xxxxx Duration: State: FailedPendingResume Error: The UPG Item does not exist. Identification fields and values: No.=’xxx’
Anyone who worked a bit with upgrading probably knows this message, or have faced it once or twice. Now it is time to explain what is going on and how we can resolve it.
All the tables starting with the name UPG* are auxiliary tables and part of the Upgrade Toolkit.
These tables are there to help the upgrade logic and acting as temporary storage while running the upgrade procedures.
They are a part of the upgrade process, and they depend on the localization of your database and the upgrade toolkit you wish to use for your upgrade.
When you import the Upgrade Toolkit objects, you will see that a bunch of UPG tables are created, such as here in Upgrade900100.DE.fob:
Normally you start with importing these objects from your Upgrade Toolkit and after that you do the table schema synchronization.
Once these tables are synchronized, the table should physically exist on SQL Server.
After that you normally proceed with your data upgrade and now you normally see that message.
The data in TAB104092 is important for your upgrade codeunits – the explanation can be found in codeunit 104075, as an example:
COD104075:
[TableSyncSetup] GetTableSyncSetupW1(VAR TableSynchSetup : Record “Table Synch. Setup”) // The purpose of this method is to define how old and new tables will be available for dataupgrade // The method is called at a point in time where schema changes have not yet been synchronized to // the database so tables except virtual tables cannot be accessed // TableSynchSetup.”Table ID”: // Id of the table with schema changes (i.e the modified table). // TableSynchSetup.”Upgrade Table ID”: // Id of table where old data will be available in case the selected TableSynchSetup.Mode option is one of Copy or Move , otherwise 0 // TableSynchSetup.Mode: // An option indicating how the data will be handled during synchronization // Check: Synchronize without saving data in the upgrade table, fails if there is data in the modified field/table // Copy: Synchronize with saving data in the upgrade table, the modified table contains data in matching fields // Move: Synchronize with moving the data in the upgrade table,the changed table is empty; the upgrade logic is handled only by application code // Force: Synchronize without saving data in the upgrade table, disregard if there is data in the modified field/table // Examples: // DataUpgradeMgt.SetTableSyncSetup(DATABASE::”Sales Header”,DATABASE::”UPG Sales Header”,TableSynchSetup.Mode::Copy); ………… DataUpgradeMgt.SetTableSyncSetup(DATABASE::Item,DATABASE::Table104092,TableSynchSetup.Mode::Copy); …………
The question here is, if it plays a role if your table is empty or not.
If we follow the data Upgrade step from this known KB article: https://msdn.microsoft.com/en-us/dynamics-nav/upgrading-the-data
Step 10:
##
Task 10: Run the schema synchronization to synchronize the new tables
Similar to task 8, to publish the data schema changes of the newly imported tables to the SQL tables, run the Sync. Schema For All Tables – With Validation option from the development environment or run the Sync-NavTenant cmdlet from the Microsoft Dynamics NAV 2017 Administration Shell.
##
It is very clearly stated, that the schema sync should be executed with validation. This step of synchronizing the schema can be that key for this message.
Usually to figure this out, before you run the sync with Validation or with Force, you start with running Sync.– mode Checkonly, just to understand how the situation after the table synch looks like.
The result can looks like the following result.
Sync-NAVTenant DynamicsNav100 -Mode CheckOnly
Sync-NAVTenant : The schema synchronization may result in deleted data. The following destructive changes were detected:
Table: 18, Customer Field: 5001900, No. Entries for Avis: Deleted Field: 5055250, Liq. Payment Terms Code: Deleted Field: 5157970, Electronic Document Dispatch: Deleted Table: 23, Vendor Field: 5001900, No. Entries for Avis: Deleted Field: 5001901, Direction Code: Deleted Field: 5001902, Payment Type: Deleted Field: 5055250, Liq. Payment Terms Code: Deleted Field: 5157970, Electronic Document Dispatch: Deleted Table: 38, Purchase Header Field: 99008500, Date Received: Deleted Field: 99008501, Time Received: Deleted Field: 99008504, BizTalk Purchase Quote: Deleted Field: 99008505, BizTalk Purch. Order Cnfmn.: Deleted Field: 99008506, BizTalk Purchase Invoice: Deleted Field: 99008507, BizTalk Purchase Receipt: Deleted Field: 99008508, BizTalk Purchase Credit Memo: Deleted Field: 99008509, Date Sent: Deleted Field: 99008510, Time Sent: Deleted Field: 99008511, BizTalk Request for Purch. Qte: Deleted Field: 99008512, BizTalk Purchase Order: Deleted Field: 99008520, Vendor Quote No.: Deleted Field: 99008521, BizTalk Document Sent: Deleted Table: 79, Company Information Field: 50000, Ort für Signatur: Length reduced Field: 50001, GSF: Length reduced Field: 50002, HRB: Length reduced Field: 60003, IBAN 2: Length reduced Field: 60008, IBAN 3: Length reduced Field: 60013, IBAN 4: Length reduced Field: 70300, Path scanned Purch. Doc.: Length reduced Table: 91, User Setup Field: 5310951, CrefoDefaultCustRefConsumer: Length reduced Table: 110, Sales Shipment Header Field: 5900, Service Mgt. Document: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 99008509, Date Sent: Deleted Field: 99008510, Time Sent: Deleted Field: 99008515, BizTalk Shipment Notification: Deleted Field: 99008519, Customer Order No.: Deleted Field: 99008521, BizTalk Document Sent: Deleted Table: 120, Purch. Rcpt. Header Field: 99008500, Date Received: Deleted Field: 99008501, Time Received: Deleted Field: 99008507, BizTalk Purchase Receipt: Deleted Table: 204, Unit of Measure Field: 60000, Inventory Unit PV: Deleted Table: 295, Reminder Header Field: 5157970, Electronic Document Dispatch: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 5157972, Send As Copy: Deleted Table: 297, Issued Reminder Header Field: 5157970, Electronic Document Dispatch: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 5157972, Send As Copy: Deleted Table: 5107, Sales Header Archive Field: 5158202, Archiv DocID: Length reduced Field: 5157970, Electronic Document Dispatch: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 5157972, Send As Copy: Deleted Field: 99008500, Date Received: Deleted Field: 99008501, Time Received: Deleted Field: 99008502, BizTalk Request for Sales Qte.: Deleted Field: 99008503, BizTalk Sales Order: Deleted Field: 99008509, Date Sent: Deleted Field: 99008510, Time Sent: Deleted Field: 99008513, BizTalk Sales Quote: Deleted Field: 99008514, BizTalk Sales Order Cnfmn.: Deleted Field: 99008518, Customer Quote No.: Deleted Field: 99008519, Customer Order No.: Deleted Field: 99008521, BizTalk Document Sent: Deleted Table: 5109, Purchase Header Archive Field: 99008500, Date Received: Deleted Field: 99008501, Time Received: Deleted Field: 99008504, BizTalk Purchase Quote: Deleted Field: 99008505, BizTalk Purch. Order Cnfmn.: Deleted Field: 99008506, BizTalk Purchase Invoice: Deleted Field: 99008507, BizTalk Purchase Receipt: Deleted Field: 99008508, BizTalk Purchase Credit Memo: Deleted Field: 99008509, Date Sent: Deleted Field: 99008510, Time Sent: Deleted Field: 99008511, BizTalk Request for Purch. Qte: Deleted Field: 99008512, BizTalk Purchase Order: Deleted Field: 99008520, Vendor Quote No.: Deleted Field: 99008521, BizTalk Document Sent: Deleted Table: 5900, Service Header Field: 5157970, Electronic Document Dispatch: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 5157972, Send As Copy: Deleted Table: 5992, Service Invoice Header Field: 5157970, Electronic Document Dispatch: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 5157972, Send As Copy: Deleted Table: 5994, Service Cr.Memo Header Field: 5157970, Electronic Document Dispatch: Deleted Field: 5157971, Bill-to E-Mail: Deleted Field: 5157972, Send As Copy: Deleted Table: 50001, Customizing Setup Field: 1040, Shipping Date: Deleted Field: 1050, Edit Post. Groups in Gen. Jnl.: Deleted Table: 51000, External Entries Setup Field: 1, Key: Data type changed Table: 75400, Mail Batch Field: 50000, Sofortversand: Deleted Field: 50001, mit Signatur: Deleted Field: 50003, Erstell-Datum: Deleted Field: 50004, Sonderbehandlung bei info@: Deleted Table: 75401, Mail Batch Line Field: 30, CI Name: Length reduced Field: 50000, Segment No.: Deleted Field: 50001, Salesperson Code: Deleted Field: 50002, Suchbegriff Unternehmen: Deleted Field: 50003, Contact Company No.: Deleted Field: 50004, zHd: Deleted Table: 75402, MailIT Setup Field: 50000, Quote Report ID: Deleted Field: 50002, Order Report ID: Deleted Field: 50004, Abnahme Report ID: Deleted Field: 50006, Bestellung Report ID: Deleted Table: 75403, MailIT Report Field: 1, ID: Data type changed Field: 3, User ID: Deleted Field: 2, Caption: Deleted Table: 5157823, Reserved 5157823 Field: 1, Dummy: Data type changed Table: 5157825, Reserved 5157825 Field: 1, Dummy: Removed from primary key - Data type changed Table: 5157917, Reserved 5157917 Field: 1, Dummy: Data type changed Table: 5158202, Document Archive Setup Field: 22, Sales Process Nos.: Length reduced Field: 23, Purchase Process Nos.: Length reduced Table: 5158221, Invoice Monitor Setup Field: 2, Default Journal Template Name: Length reduced Field: 3, Default Journal Batch Name: Length reduced Table: 5158225, DropZone Lookup Values Field: 1, Archive Document Type: Data type changed Field: 3, Document Type: Data type changed Field: 6, Value: Deleted Field: 5, Source Type: Deleted
Please do not consider a table synch with the option force set to true!
This is exactly what we do not want because we do not know exactly what data could be deleted when running the sync with force, and if this could empty any needed tables or any needed auxiliary tables.
Even if the Item table and the UPG Item table are not in the list, we cannot guarantee what will be happening when you run this forced sync, and what relations you have between the tables, either on the standard version or based on any customization.
Before you go further, you must solve the errors in the list, till you are able to run a normal Sync with Validation, only with this you could avoid that any needed tables / auxiliary tables would be emptied.
How can we solve the Errors in the List?
- Write your own Upgrade codeunit, as explained in CU 104075,to control how Dynamics NAV what to do with the Fields that might be deleted during this Sync.The Concept of UpgradeCodeunits was introduced first time in NAV2015. here is an example of it:
https://community.dynamics.com/nav/b/navvideos/archive/2014/11/05/how-do-i-synchronize-database-schema-using-upgrade-codeunits-in-microsoft-dynamics-nav-2015
This is an Example on how to customize CU 104xxx:DataUpgradeMgt.SetTableSyncSetup(DATABASE::"G/L Account", DATABASE::"MyUPG G/L Account",TableSynchSetup.Mode::Copy); DataUpgradeMgt.SetTableSyncSetup(DATABASE::Customer, DATABASE::"MyUPG Customer",TableSynchSetup.Mode::Copy); DataUpgradeMgt.SetTableSyncSetup(DATABASE::Vendor, DATABASE::"MyUPG Vendor",TableSynchSetup.Mode::Copy);
Here the „MyUPGxxx“ Table is an own newly created Auxiliary Table, that we are going to use to store the data and structure of any table that might be affected if we carry on with a force Sync, The rest of the customization there can be controlled by you, based on the data from that table, and it is also explained in the above link.
- Another possibility is just to merge these objects with the Cronus from the new version.
Simply you can take the old object ‚ex. Customer or Vendor table, or Whatever table mentioned in the error list and merge it using a Compare-Merge with the same table from the newer version.
Then export the result as a fob out of the new version.
Then when you import that fob in your upgraded database, then a sync with validation should not be an issue any more.
Then you carry on with your Data upgrade, and the upgrade runs without issues.
Special Thanks for Duilio Tacconi for his great contribution in the area of schema sync. and data upgrade.
Best regards,
Abdelrahman Erlebach
We're always looking for feedback and would like to hear from you. Please head to the Dynamics 365 Community to start a discussion, ask questions, and tell us what you think!