NAV 2013 R2 change Collation - Case Sensitive - bug

vremeni4

If I change collation in NAV 2013 (File-> Database -> Alter ) from Case Sensitive into Case Insensitive
The collation also changes on the SQL server from Latin1_General_100_CS_AS into Latin1_General_100_CI_AS
which is perfectly fine and expected.

But if I do the same thing in NAV 2013 R2 the collation remains the same Latin1_General_100_CS_AS.
In other words it looks like changing collation (e.g. remove Case Sensitive flag) from NAV 2013 R2 does not work any more.

The problem is not visible until you start e.g. Rapid Start and you get error message of type

The data type nvarchar cannot be used as an operand to the UNION, 
INTERSECT or EXCEPT operators because it is not comparable

The SQL profiler then shows that NAV uses Latin1_General_100_CI_AS but SQL Server expects Latin1_General_100_CS_AS.

Could someone confirm this nasty bug in NAV 2013 R2.

Thanks.

gilbert

Hello,

I can confirm the same issue in NAV 2013 R2.

eivind

I am experiencing the same issue still, with Update Rollup 2.
Problem is also occurring within Microsoft own upgrade toolkit. When executing step 2 of the data migration, an error that has to do with UNION in SQL appears, and it has the same reason as stated.

It has clearly not been fixed.

Has anyone filed a support case with MS on this?

Eivind

alex32

I can confirm the problem.
Like eivind said i saw this problem first durich Upgrade New Step 2 - Data Migration.

I am running Update Rollup 4 - still nothing changed, cannot change form CS AS to CI AI.

Error occours when:
-Trying to access "Table Information" Virtual Table
-Trying to add Tables to a rapidstart package

Klaus79

Hello guys,

during Upgrade-Process we also got the error message of type

"The data type nvarchar cannot be used as an operand to the UNION,
INTERSECT or EXCEPT operators because it is not comparable"


After contacting Microsoft support we got a solution that worked for us, hope
it will solve your problems too:

Check the Database Collation in the database table columns while upgrading.
NAV 2013 and higher versions will support only Windows Collation. If you are upgrading from previous version and your SQL Server NAV database stick with a SQL Server Collation, it is a mandatory step to change the collation to a suitable Windows Collation.
If you have to perform this step or if your database has changed the collation in the past for somewhat reason, it is very important to check the status of the collation right after the conversion.

To inspect the status of the database you might run this query against the NAV database:

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLLATION_NAME <> '<MyDatabaseCollation>'
ORDER BY TABLE_NAME

Where <MyDatabaseCollation> is the current NAV Database Collation. If you have some table columns that report differences then you need to amend these manually with scripts like

ALTER TABLE dbo.[MyTable] ALTER COLUMN [MyField]
nvarchar(xx) COLLATE MyDatabaseCollation NOT NULL;

Keep in mind that if these refers to PK, Indexes or Indexed Views (V-SIFT) then you have to drop them and recreate it afterwards.

Best regards
Klaus