NAV2015 upgrade step-2

Roelof

In Upgrade Step2, upgrading from Nav2009 to NAV2015), there is a function called 'UpdateUserID'. Does anyone know if this function is required to be run (and what it is doing)? I noticed during a test-upgrade run that this function taking a long time before it completes.

mohana_cse06

You are right. this function will validate all G/L Entries with all users in database.
we have commented this function to upgrade one of our customers which is having 300GB

rsaritzky

This function replaces occurences of the "User ID" field with "Domain\User ID". If you look in a 2009 database, the "User setup" table has user id's without domain prefix(es). In 2013 and later, it has the domain prefix.

Ron

Roelof

So, what impact would it have by not running it during the upgrade? (Upgrade from 2009R2 to NAV2015. Legacy database is on SQL).

Rikt-It

We did it with a SQL-script...

--**************************************************************************************************************
-- Christer Berntsson, Softronic, Stockholm, Sweden, 2015-06-17
--
-- Idea from Lars Westman, Umeå, Sweden
--
-- Update all 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.
--
-- Test it before You use it
--**************************************************************************************************************
USE [Nav2015_XYZ] -- <<-- Your database
GO

--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)
SELECT
o.name
,c.name
,c.max_length
FROM
sys.columns c
Join sys.objects o on o.object_id = c.object_id
WHERE
c.name like '%User_ID%'
and o.type_desc like 'USER_TABLE' --only usertables
and c.system_type_id = 231 --only textfields
-- add Your own filters and exceptions
ORDER BY
o.name

--DEV:
--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
DECLARE UpdateCursor CURSOR FORWARD_ONLY FOR
SELECT Tablename, Fieldname, Size FROM [#User ID Fields]

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

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

--Clean up
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
DROP TABLE [#User ID Fields]