My Steps:
- installed SQL 2016 with collation SQL_Latin1_General_CP850_CI_AS
- Restored Database to Stage instances with same collation as Database.(original Backup from 2012)
- Ran
> "C:\Program Files\Microsoft SQL Server\MSSQL13.STAGE\MSSQL\Binn\sqlservr.exe" -m -T4022 -T3659 -s"STAGE" -q"SQL_Latin1_General_CP1_CI_AS"
this was to Changed the Instances and all database collation to SQL_Latin1_General_CP1_CI_AS. (what doesn't get changed User Type Tables) Run Query
Select i.name, c.collation_name,i.internal_type,i.internal_type_desc from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS' AND object_name(c.object_id) NOT LIKE 'sys%' AND object_name(c.object_id) NOT LIKE 'queue%' AND object_name(c.object_id) NOT LIKE 'file%' AND object_name(c.object_id) NOT LIKE 'spt%' AND object_name(c.object_id) NOT LIKE 'MSrep%'
Will return the internal tables with Old collation.
name collation_name internal_type internal_type_desc sqlagent_jobs SQL_Latin1_General_CP850_CI_AS 220 CONTAINED_FEATURES
Test 2:
Create New database on 2016 when the Instances collation is set to SQL_Latin1_General_CP850_CI_AS.
new database collation has SQL_Latin1_General_CP850_CI_AS.Run this Query where collation = SQL_Latin1_General_CP1_CI_AS
Select i.name, c.collation_name,i.internal_type,i.internal_type_desc from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id WHERE c.collation_name = 'SQL_Latin1_General_CP1_CI_AS' AND object_name(c.object_id) NOT LIKE 'sys%' AND object_name(c.object_id) NOT LIKE 'queue%' AND object_name(c.object_id) NOT LIKE 'file%' AND object_name(c.object_id) NOT LIKE 'spt%' AND object_name(c.object_id) NOT LIKE 'MSrep%'
this will return results that don't match the current collation. name collation_name internal_type internal_type_desc sqlagent_jobs SQL_Latin1_General_CP1_CI_AS 220 CONTAINED_FEATURES
I don't think the application will be affected by this internal_table. But still strange that the internal_table collation does not match, on Creating and after converting the instances to a new collation.
If I do the same process but this time on a 2012 Staging instances.
- restore Database to a 2012 Stagging instances with collation SQL_Latin1_General_CP850_CI_AS
- Ran >
"C:\Program Files\Microsoft SQL Server\MSSQL11.STAGE12\MSSQL\Binn\sqlservr.exe" -m -T4022 -T3659 -s"STAGE" -q"SQL_Latin1_General_CP1_CI_AS"
- fix User Type Tables
- Backup converted database.
- restore database on SQL 2016 with collation SQL_Latin1_General_CP1_CI_AS.
Run Query
Select i.name, c.collation_name,i.internal_type,i.internal_type_desc from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS' AND object_name(c.object_id) NOT LIKE 'sys%' AND object_name(c.object_id) NOT LIKE 'queue%' AND object_name(c.object_id) NOT LIKE 'file%' AND object_name(c.object_id) NOT LIKE 'spt%' AND object_name(c.object_id) NOT LIKE 'MSrep%'
Nothing returns because I converted it in 2012.
If anyone can explain what is going? Is this a bug? Anyone know how to fix the internal table collation?