SQL script to create dimension entry NAV2009 to NAV2016

rsaritzkyrsaritzky Member Posts: 469
Hi all,

We're testing our planned NAV2009->NAV2016 upgrade, and we have a large database (180GB approx). The first data upgrade test was done on a database that was shrunk down by date-compressing several of the large ledger files. The data upgrade ran in about 50 hours, which doesn't fit into our planned time-window of a weekend conversion (this does not include the other data upgrade steps which take additional time.)

In addition, we have several custom ledger tables that I have not yet converted to dimension set entries, so I know our next full test will take even longer.

We are investigating more server resources, but our test server is brand-new, enterprise-class, lots of RAM, high-speed SAS drives and is only being taxed to about 20% usage during the data upgrade.

I have seen a few references about some people using SQL scripts to create the dimension set entries instead of the Data Upgrade codeunit. But I haven't seen any sample scripts. Has anyone seen any actual SQL script code that I could reference? I'm just trying to avoid having to reverse-engineer everything.



Best Answers


  • Options
    rsaritzkyrsaritzky Member Posts: 469
    Thanks David,

    I did start looking through the existing data upgrade codeunit - I know now that it constructs SQL code to create temporary tables that store dimension information in a variety of ways - I haven't gotten all the way through the script with an "example" table to see what the SQL statements actually do.

    But so far (just for other people to know and perhaps contribute):

    1. It creates 3 temporary tables - UpgradeDimTable, UpgradeDimSetID and UpgradeTableID, each named uniquely by company and table, e.g. 'CompanyName$upgrade355_Dim' for the first one.

    It then populates those tables by doing various SELECT INTO's - I haven't parsed them all out yet, so I can't post the results (yet).

    However, it is currently apparent that due to the complexity of the code, not very many people have chosen to try to reverse-engineer this process to see if it can be tuned.

    BUT, in my continuing experimentation, I've discovered that the part of the process that adds the Domain Name to the User ID takes FAR more time than the Dimension update piece. My user-rename process errored out after 56 hours, but the Dimension update process for that same database (with 27,000,00 Ledger Entry Dimension records) took only 3-4 hours to complete.

    An experienced NAV developer, Lars Wedman, has posted an article about a basic script/technique to move the User Rename logic into a SQL script, which he claims is 10 to 100 times faster than the NAV code. I'm going to test his code in the next few days. It could be a little-more refined (which I may do in hopes of contributing it to the community), but his reasoning and logic is very sound. If you do updates like this infrequently, it would only take an hour to use his "manual method" to construct the SQL script. If you want to read the article yourself, here's the link:


  • Options
    Rikt-ItRikt-It Member Posts: 37
    We did it with a SQL-script..

    On Your own risk...
    -- Christer Berntsson, Softronic, Stockholm, Sweden ,2015-06-17
    -- Idea from Lars Westman, Umeå, Sweden
    -- Update columns in NAV-2013--> with domainname
    -- This script should be run in SQL-server Management Studio
    -- 2015-06-17: In a Nav2015-database with 8 Companies and Size of 87GB on SSD-discs it took aprox 5 minutes.
    USE [Nav2015_XYZ] -- <<-- Your database

    --Use a temporay table
    CREATE TABLE [#User ID Fields] (Tablename varchar(250), Fieldname varchar(50), Size int)

    --Fill temporay table with tablename and field
    INSERT INTO [#User ID Fields] (Tablename, Fieldname, Size)
    sys.columns c
    Join sys.objects o on o.object_id = c.object_id
    c.name like '%User_ID%'
    and o.type_desc like 'USER_TABLE' --only usertables
    and c.system_type_id = 231 --only textfields
    -- add filters and exceptions

    --select * from [#User ID Fields] -- Show table who contain '%User_ID%' and is text-fields

    DECLARE @sql nvarchar(MAX),
    @CompanyName nvarchar(254),
    @Tablename nvarchar(254),
    @Fieldname nvarchar(254),
    @Domain nvarchar(254),
    @Size int

    SET @Domain ='TheDomainName'

    --Start loop all Companies and Tables and Fields
    SELECT Tablename, Fieldname, Size FROM [#User ID Fields]

    OPEN UpdateCursor
    FETCH NEXT FROM UpdateCursor INTO @Tablename, @Fieldname, @Size

    PRINT getdate()
    SET @sql = 'UPDATE SET = CASE WHEN LEN()<>0 THEN '''+ @Domain+'\''+REVERSE(LEFT(REVERSE(),CHARINDEX(''\'',REVERSE(''\''+))-1)) ELSE END'
    PRINT @sql
    EXEC (@sql)
    FROM UpdateCursor
    INTO @Tablename, @Fieldname, @Size

    --Clean up
    CLOSE UpdateCursor
    DEALLOCATE UpdateCursor
    DROP TABLE [#User ID Fields]
    Christer in Stockholm, Sweden
Sign In or Register to comment.