mibuso.com
Home› NAV Three Tier
Options

Big Upgrade issue: Step 2 NAV2013R2 UNION, INTERSECT or EXC

mglashouwemglashouwe Member Posts: 10
edited 2014-09-23 in NAV Three Tier
Hi Guys,

I'm reading these forums for years now, and I always seem to find a solution for my NAV problems, but this time I've gone too far :-$

I'm running a HUGE NAV conversion from NAV3.70 to NAV2013R2 with a database the size of a truck (500Gigs).

Now I'm finally in NAV2013R2 and right at the start of Step2 I get this error:

Microsoft Dynamics NAV

Break On Error Message:

The following SQL error was unexpected.
The data type nvarchar cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Statement(s) could not be prepared.
OK

I've read about this error on different places. And they all lead to Collation issue. I tried various things: newest builds for NAV2013, NAV2013R2, first RTM then the new Builds, Tried to change collation...nothing helps...

My conversionlist contains 70 steps to take and 3 days of running time, so it's almost impossible to trace. Personally I think it's somewhere between opening for the first time in NAV2013 and running Powershell to sync Tenant.

Has anyone crossed this before? If so, could help me in the right direction?

It's highly appreciated, since I ran out of options... :roll:


With kind regards,

Michael

Comments

  • Options
    dave_cdave_c Member Posts: 45
    We've had collation issues as well when upgrading to 2013 R2. We used the below query, which I think I found elsewhere on Mibuso. Change 'Latin1_General_100_CI_AS' to the collation on the database properties. 
    SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
COLLATION_NAME <> 'Latin1_General_100_CI_AS' 
ORDER BY TABLE_NAME
    That should give you a list of fields with the wrong collation type. We changed the collation type of the fields to the correct one through SQL eg: 
    ALTER TABLE [Company Name$HRContractServiceNotice] ALTER COLUMN NoticePeriodER
            varchar(32)COLLATE Latin1_General_100_CI_AS NOT NULL;
    dropping and recreating views and indexes where necessary eg: 
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Company]') AND name = N'Company$0')
ALTER TABLE [dbo].[Company] DROP CONSTRAINT [Company$0]
GO


            
ALTER TABLE [Company] ALTER COLUMN Name
            varchar(30)COLLATE Latin1_General_100_CI_AS NOT NULL;          

GO

ALTER TABLE [dbo].[Company] ADD  CONSTRAINT [Company$0] PRIMARY KEY CLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

    We didn't manage to find what caused the collation to be incorrect but the above did fix it. Hope some of that helps!
  • Options
    mglashouwemglashouwe Member Posts: 10
    Hi Dave!,

    Thanks for your response! Since the problem is in a lot of columns / tables...isn't it possible to change the collation for all the columns / tables involved in 1 query? It would take forever to do it all manually

    Thanks!
  • Options
    mglashouwemglashouwe Member Posts: 10
    Hi guys,

    Just to let you know: I fixed the issue finally! \:D/

    After upgrading:
    - Stop the Service Tier
    - Set the database to Single User
    - Alter Database -> Sorting -> change the collation from the NAV Developer Environment to a different value.
    - Set the database to multiple users again
    - Start the Service Tier
    - Run Powershell (-SyncTenant)

    Warning: This will increase the size of the database a lot! After it's done, you should perform a database schrink.
Sign In or Register to comment.