NAV 2015 Collation Change unexpected results

paurola

On Finnish demo database the default collation is Finnish_Swedish_100_CS_AS. The Case Sensitivity is something we never want, so we cannot use the demo database as it is and normally we use our own "base database" with collation set to Finnish_Swedish_100_CI_AS as a basis for new NAV databases.

What happened was, that "someone" who was not aware of this, created a development db by copying the demo database with the case sensitivity on. I noticed this after a lot of data was inserted into the database - so I decided to try to do the questionable and change the existing collation in the NAV "Alter Database - Collation" tab. After some fighting with the single user option, the job was done, and the database and RTC-client seemed to be ok.

After a week another user added new companies to the database and quess what... RTC client started throwing errors all over the place. The errors are: "Cannot resolve the collation conflict between "Finnish_Swedish_100_CI_AS" and "Finnish_Swedish_100_CS_AS" in the equal to operation. Statement(s) could not be prepared."

Seems like NAV has totally messed up and now I have a mix of collations in our database. I confirmed this by running Klaus79's SQL query from thread:
http://www.mibuso.com/forum/viewtopic.php?f=32&t=59489&start=0

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLLATION_NAME Like 'Finnish%' ORDER BY TABLE_NAME

The results of this query show that there is something really wrong with the database. You can see the results in file sql_results.jpg. The results show that some of the tables and fields have the collation of "Finnish_Swedish_100_CI_AS" and some "Finnish_Swedish_100_CS_AS". What's worse, is that some of these "false" collation tables are system tables, like $ndo$tenantproperty.

I will create a new database and move the data to it, since it is obvious that it is not worth it to try to fix this existing database. I post this as a warning on changing the collation on live database but I am also curious if somebody happens to have more information on this phenomenon.

/Pauli

Rikt-It

Hej

I do not know if below will help you.... But I use this way to change collation in Nav2015... 8)

1. Inside RTC-client in Your database with wrong collation, Run a "Export to data file" select everything (data and application...)

1.b Or use PowerShell: Export-NAVData -ServerInstance Nav2015_Corrupt_instans -FilePath C:\TEMP\Nav2015_To_NewDb_AllData.navdata -IncludeApplication -IncludeGlobalData -CompanyName "CRONUS Sverige AB"

2. Start the Dev-Client with "Your database with wrong collation" and export a "superfob"

3. Start a new Dev-Client with "run as administrator". Create a "new database" with with right collation (Same as SQL-Server)

4. Set up a new NST-ServiceTier pointing to the new database. I always use ServiceTierAdministrator from Tegos found at http://www.mibuso.com/dlinfo.asp?FileID=1410, and I use portsharing.

5. Import the "superfob" into Your "new database"

6. Compile all objects

7. Use PowerShell to import Your data:
Import-NAVData -ServerInstance Nav2015_DBName -FilePath C:\temp\Nav2015_To_NewDb_AllData.navdata -IncludeApplicationData -CompanyName "CRONUS Sverige AB"

8. Use the "Sync for All Scheme Tables" - Force

9. Restart Your NST-service


check Your old and new database with SQL-script

-- *******************************
-- Check collation
-- *******************************

USE  [YourDatabase]
GO

SELECT
	collation_name,
	count(name) as 'Count'
FROM 
	sys.all_columns
WHERE 
	collation_name is not null
	and [object_id] in (SELECT [object_id] FROM sys.tables WHERE [type] = 'U')
GROUP BY
	collation_name

emeriksp

Is "superfob" a way to say all objects?