Speeding up Step 2 in NAV upgrades

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:

  1. Create a page on the Field table
  2. Run the page and filter on RelationTableNo = 2000000120 and RelationFieldNo = 2.
  3. Copy the data to Excel and use formulas to get usable table and field names for SQL
  4. Insert the table and field names to a table
  5. 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:

2014-12-13_17-40-27

The rows copied to excel and formula added:

2014-12-13_17-43-23

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.

Happy upgrading! :-)

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.

4 Responses to “Speeding up Step 2 in NAV upgrades”

  1. Dave Machanick 2016-02-26 at 02:02 #

    Thanks for the routine. I have a large upgrade and it crashed after 24 hours because it had hit a 2GB limit.
    I have run your routine, and will run step 2 (minus the user id change) once they have beefed up the server.
    I did make one minor change – printer selections – I added a where clause for the user id ”.
    I could not get your substitute function to work, so I split it into 2.
    The sql took 26 minutes to run – I am curious to see how much it decreases the step 2 time.
    I have an incident with Microsoft – they recommend running the user id codeunit separately.
    Since your method is much better, do you mind if I refer them to your site?

    Thanks,
    Dave

    • Lars Westman 2016-02-29 at 22:49 #

      I have no problems with you talking to Microsoft about it.

      My solution is a bit manual, but it’s not like you do it every day so I can live with it for now. Of course it would be possible to streamline it further and maybe even execute the SQL-code directly from within NAV, but I haven’t had the time to invest in that. Maybe Microsoft has?

  2. Janne Johnsen 2016-03-01 at 10:50 #

    Hi,
    I am in a similar situation and have used your script.
    But I experienced problems with Company as my Company Name involved ‘.’ and ‘/’. As this was not the same in the table names in the SQL (where it was changed to ‘_’), the UPDATE could not find the tables.
    Eg. Cronus A_S.table is not the same as Cronus A/S.table

    I made a workaround but it was not a nice one. Do you have any elegant workaround to this?

    BR.
    Janne

Leave a Reply