Upgrade NAV 2009R2 to NAV 2017 - Login Failed

ACaignie

Hello,
I am trying to upgrade a database from NAV2009R2 to NAV2017, but this keeps failing for some reason I cannot figure out.
This is what has been done so far (following the
- Database is tested (no errors)
- Upgrade601700.BE.1: data transfer is done + objects are deleted
- Open database in NAV 2013
- First time 'altering objects'
- Data conversion is done
- Second time 'altering objects' runs to 100%
- Third time 'altering objects' stops halfway with a 'login failed' error.

On SQL server an error is logged: "Login failed for user xxx'. Reason: Failed to open the explicitly specified database 'Upgrade2009R2'. [CLIENT: 10.1.1.19]"
On the client nothing is logged in the event viewer.

Some more information
- All tables compile before the update
- All tables linked to a view are deleted (linkedobjects=Yes)
- All statistics are deleted
- Compatibility level is correct
- NAV2009 version = 6.0.0.33793
- NAV2013 version = 7.0.0.36374
- Database = +/- 150GB. Available space on sql server = 500+ GB
- We have 40.000.000+ GL entries, so I use the trick of Vjeko (http://vjeko.com/how-i-reduced-data-upgrade-time-by-78-hours)

Does anyone have an idea? Any help will be greatly appreciated.
Andy

ACaignie

I finally found the solution: some records contained an 'enter' (char 13 + char 10) in a key field, probably due to copy from excel or an explorer. Removing these enters solved the issue.

ACaignie

I just saw I forgot to add the actual NAV error, so here it is:

The following SQL Server error or errors occurred:

18456,"28000",[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'xxx'.
4060,"42000",[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "Upgrade2009R2" requested by the login. The login failed.

davmac1

have you checked the security permissions in SQL Server for that user?

ACaignie

Hi,
Yes the user is sysadmin, and on the database has db_datareader, db_datawriter, db_ddladmin and db_owner rights.

ACaignie

Hello,
I have run the profiler while the upgrade is running, and the procedure stops at a 'CREATE TABLE' statement:



The SQL statement is:

CREATE TABLE [Upgrade2009R2].[dbo].[Card - Tractor Combination_$TEMP$] ([timestamp] ROWVERSION NOT NULL,[Card Type] INTEGER NOT NULL,[In use by] NVARCHAR(20) NOT NULL,[Supplier] NVARCHAR(30) NOT NULL,[Card No_] NVARCHAR(30) NOT NULL,[Expiring Date] DATETIME NOT NULL,[Entry No_] INTEGER IDENTITY NOT NULL,[Date In Use] DATETIME NOT NULL,[Date Out of Use] DATETIME NOT NULL,[Out of use] TINYINT NOT NULL,[Company] NVARCHAR(30) NOT NULL,[Pin Code] NVARCHAR(20) NOT NULL,[Pin 2] NVARCHAR(20) NOT NULL,[GSM Nummer] NVARCHAR(20) NOT NULL,[Puk 1] NVARCHAR(20) NOT NULL,[Puk 2] NVARCHAR(20) NOT NULL,[Default] TINYINT NOT NULL CONSTRAINT [Card - Tractor Combination$0] PRIMARY KEY CLUSTERED ([Card Type],[In use by],[Supplier],[Card No_],[Expiring Date],[Entry No_]))

Does anyone have an idea why it can fail here?
Any help would be greatly appreciated.

Andy

ACaignie

Hi,
Can it be related to the column 'Entry No.', which is created as "[Entry No_] INTEGER IDENTITY NOT NULL"
If I execute the drop constraint and create table in SQL this works fine, but the normal SQL statement that follows in the update is the insert from the Original table in the temp table. If I execute this in SQL I get the error "Cannot insert explicit value for identity column in table .. when IDENTITY_INSERT is set to OFF". So if I drop the temp table and create it again without "IDENTITY" then the insert works fine.
Can the upgrade not cope with autoincrement fields? Record Link also uses this

Andy

ACaignie

I finally found the solution: some records contained an 'enter' (char 13 + char 10) in a key field, probably due to copy from excel or an explorer. Removing these enters solved the issue.