Contained DB Collation error

When changing a database to partially contained I am getting the following error:


Cannot resolve the collation conflict between "Latin1_General_CI_AS"
and "Latin1_General_100_CI_AS_KS_WS_SC" in the EXCEPT operation.


Errors were encountered in the procedure 'RSExecRole.DeleteExtensionModuleDDL'
during compilation of the > object. Either the containment option of the database 'VeeamOne' was
changed, or this object was present in model db and the user tried to
create a new contained database.
ALTER DATABASE statement failed. The containment option of the database 'VeeamOne' could not be altered because compilation errors
were encountered during validation of SQL modules. See previous
errors.
ALTER DATABASE statement failed. (.Net SqlClient Data Provider)

The object this is reporting on I think is from SSRS. However the DB I am changing the collation on is a completely separate application.

Does anyone have any suggestions on how to resolve this?

=========================================================================
OK this is the code for the proc, not sure what about it causes it to no be able to be contained though

```
USE [VeeamOne]
GO
/ Object: StoredProcedure [reporter].[DeleteExtensionModuleDDL] Script Date: 02/12/2015 12:06:19 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [reporter].[DeleteExtensionModuleDDL]
@EMID int
AS
BEGIN
SET NOCOUNT ON;
declare @Debug bit;
set @Debug = 0;
declare @Emulate bit;
set @Emulate = 0;
declare @reportPackDestructorFunctionName nvarchar(max)
exec @reportPackDestructorFunctionName = [reporter].GenerateExtensionModuleDestructorName @EMID
if exists(select * from sys.objects where (object_id = OBJECT_ID(@reportPackDestructorFunctionName) and type in (N'P', N'PC')))
begin
exec @reportPackDestructorFunctionName
declare @objectsToDelete as table (Name nvarchar(2048), Type nvarchar(2048))
insert @objectsToDelete exec @reportPackDestructorFunctionName
if @Deb

The issue you are seeing is a conflict between the collation of the metadata in the system Views -- sys.foreign_keys and sys.objects -- and the table variable @DependencyTree.

As pointed out in @RLF's answer, the collation of database metadata changes from DATABASE_DEFAULT (in your case Latin1_General_CI_AS) to CATALOG_DEFAULT (always Latin1_General_100_CI_AS_WS_KS_SC) when altering the database to be "contained". This affects the name fields being returned in this query:

SELECT fk.object_id AS [ForeignKeyObjectID], fk.name AS [ForeignKeyObjectName],
fk.referenced_object_id AS [ParentTableID], parent.name AS [ParentTableName],
fk.parent_object_id AS ChildTableID, child.name AS [ChildTableName], @Generation
FROM @DependencyTree dt
INNER JOIN sys.foreign_keys fk
ON fk.referenced_object_id = dt.ChildTableID
INNER JOIN sys.objects parent
ON fk.referenced_object_id = parent.[object_id]
INNER JOIN sys.objects child
ON fk.parent_object_id = child.[object_id]

EXCEPT

SELECT ForeignKeyObjectID, ForeignKeyObjectName,
ParentTableID, ParentTableName,
ChildTableID, ChildTableName, @Generation
FROM @DependencyTree


The fk.name, parent.name, and child.name fields are all initially collated as Latin1_General_CI_AS but then change to Latin1_General_100_CI_AS_WS_KS_SC when you ALTER the database to make it "contained".

The error is being thrown because the string fields in both parts of the EXCEPT need to have matching collations. But the other part of the EXCEPT is using the table variable which is defined as:

DECLARE @DependencyTree as Table(ForeignKeyObjectID INT,
ForeignKeyObjectName NVARCHAR(MAX), ParentTableID INT, ParentTableName NVARCHAR(MAX),
ChildTableID INT, ChildTableName NVARCHAR(MAX), Generation INT)


No collations are specified for the NVARCHAR(MAX) fields (which technically should be declared as sysname -- always all lower-case for that one -- since that is the datatype of the source system Views of sys.objects and sys.foreign_keys). While it is not mentioned in the Contained Database Collations Table MSDN page, unlike temporary tables, table variables get their default collation from the database, not from tempdb (which is why you didn't see this error in the past since your tempdb collation should be SQL_Latin1_General_CP1_CI_AS since that is the instance collation; you would have gotten this error before if this table were a temporary table). So the collation used for the ForeignKeyObjectName, ParentTableName, and ChildTableName fields was Latin1_General_CI_AS and will still be that same collation upon the database being "contained".

Changing that table variable declaration to be the following should resolve this issue:

DECLARE @DependencyTree Table
(
ForeignKeyObjectID INT,
ForeignKeyObjectName sysname COLLATE CATALOG_DEFAULT,
ParentTableID INT,
ParentTableName sysname COLLATE CATALOG_DEFAULT,
ChildTableID INT,
ChildTableName sysname COLLATE CATALOG_DEFAULT,
Generation INT
);


Using COLLATE CATALOG_DEFAULT will work with databases when they are not contained and when they are altered to be contained since CATALOG_DEFAULT resolves to the database default in non-contained databases. Another way of stating this behavior is that since database metadata is collated as CATALOG_DEFAULT in either state of the database, it will work in the table variable (and temporary tables) in either state of the database.

StackExchange Database Administrators Q#122186, answer score: 7