When upgrading NAV time is often a critical factor. especially when the database is big, has many companies and users. Running the data conversion can take several hours or days and meanwhile the business stands still.
One change in version 2013 from previous versions is that User ID includes the domain name. This means that the User ID has to be updated in many tables.
In step 1 of the data conversion the table 104087 “Temp Windows Login” is populated with one row per user and is then processed in codeunit 104048 “Upgrade New Version” in step 2 to update data in all tables with relation to the user table. This code is triggered from the function UpdateUserID in codeunit 104048 . In 2013 codeunit 418 “User Management” is called and in 2015 codeunit 104056 “Upgrade User Mgt.”, but it’s the same kind of code executing; The Field table is used to find all fields in other tables with relation to the User table and then the User ID is changed using RecRef and FieldRef. The problem is that really big tables are involved (G/L Entry, Customer Ledger Entry etc.) and User ID is not indexed. It’s a total of over 100 tables that includes the User ID. When there’s many users and companies this will make conversion very lengthy.
A workaround is to skip the renaming of User ID in step 2 and instead do the same using a TSQL script after step 2.
Here’s a procedure to use:
- Create a page on the Field table
- Run the page and filter on RelationTableNo = 2000000120 and RelationFieldNo = 2.
- Copy the data to Excel and use formulas to get usable table and field names for SQL
- Insert the table and field names to a table
- Iterate that table and the company table to update the User ID’s
The result was that I saved a lot of hours on the upgrade and the TSQL script only took XXXX minutes to run.
Here’s some details
The form filtered the same way as in the code:
The rows copied to excel and formula added:
The formula för e.g. E2 is SUBSTITUTE(SUBSTITUTE(“(‘”&C2;”/”;”_”);”.”;”_”)&”‘,”&SUBSTITUTE(SUBSTITUTE(“‘”&D2&”‘),”;”/”;”_”);”.”;”_”)
Create the table in SQL. Example below truncated, but all rows from Excel shall be pasted in to the TSQL code.
CREATE TABLE [User ID Fields] (Tablename varchar(50), Fieldname varchar(50)) INSERT INTO [User ID Fields] (Tablename, Fieldname) VALUES ('G_L Entry','User ID'), ('Cust_ Ledger Entry','User ID'), ('Vendor Ledger Entry','User ID'), ('G_L Register','User ID'), ('Item Register','User ID'), ('User Time Register','User ID'), ('Printer Selection','User ID'), ('Date Compr_ Register','User ID'), ('User Setup','User ID'), ('G_L Budget Entry','User ID'), ('Sales Shipment Header','User ID'), .... .... ('Sales Invoice Header','User ID')
The code to update User ID in the tables:
DECLARE @sql nvarchar(MAX), @CompanyName nvarchar(128), @Tablename varchar(50), @Fieldname varchar(50), @Domain varchar(50) DECLARE UpdateCursor CURSOR FORWARD_ONLY FOR SELECT Name, Tablename, Fieldname FROM Company, [User ID Fields] WHERE Tablename <> 'Printer Selection' SET @Domain = 'MyDomain' OPEN UpdateCursor FETCH NEXT FROM UpdateCursor INTO @CompanyName, @Tablename, @Fieldname WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sql = 'UPDATE ['+@CompanyName+'$'+@Tablename+'] SET ['+@Fieldname+'] = '''+@Domain+'\''+['+@Fieldname+']' EXEC (@sql) FETCH NEXT FROM UpdateCursor INTO @CompanyName, @Tablename, @Fieldname END CLOSE UpdateCursor DEALLOCATE UpdateCursor DROP TABLE [User ID Fields] SET @sql = 'UPDATE [Printer Selection] SET [User ID] = '''+@Domain+'\''+[User ID]' EXEC (@sql)
Upgrading isn’t super fast even after this workaround, but if you have many users and companies and a big database this can save you a lot of time when upgrading. Use this as you like and at your own risk.
I almost forgot! One more tip when upgrading a database with many companies is to add code in the end of both step 1 and 2 that sends you an e-mail when it’s done. It helps to keep track of which companies that has been processed and you’ll get notified when it’s time to run the next company. Just don’t forget a COMMIT before your e-mailing code if there should be any issues with the e-mailing.