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 @Debug = 1
begin
select * from @objectsToDelete
end
declare @TablesToDelete    as table(ObjectID int, Name varchar(max))
declare @FunctionsToDelete   as Table(Name nvarchar(max))
declare @StoredProceduresToDelete as Table(Name nvarchar(max))
declare @AssembliesToDelete   as Table(Name nvarchar(max))
declare @ViewsToDelete    as Table(Name nvarchar(max))
insert into @TablesToDelete
select object_id(Name), Name
from @objectsToDelete
where Type = 'Table'
insert into @FunctionsToDelete
select Name
from @objectsToDelete
where Type = 'Function'
insert into @StoredProceduresToDelete
select Name
from @objectsToDelete
where Type = 'Procedure'
union
select @reportPackDestructorFunctionName
insert into @AssembliesToDelete
select Name
from @objectsToDelete
where Type = 'Assembly'
insert into @ViewsToDelete
select Name
from @objectsToDelete
where Type = 'View'
declare @DependencyTree as Table(ForeignKeyObjectID int, ForeignKeyObjectName nvarchar(max),
ParentTableID int, ParentTableName nvarchar(max),
ChildTableID int, ChildTableName nvarchar(max), Generation int)
declare @Generation int;
set @Generation = 0;
insert into @DependencyTree
select  distinct(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  sys.foreign_keys as fk
inner join sys.objects as parent
on   fk.referenced_object_id = parent.object_id
inner join sys.objects as child
on   fk.parent_object_id = child.object_id
where  fk.referenced_object_id in (select ObjectID from @TablesToDelete)
while @@ROWCOUNT > 0
begin
set @Generation = @Generation + 1
insert into @DependencyTree
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 as fk
on   fk.referenced_object_id = dt.ChildTableID
inner join sys.objects as parent
on   fk.referenced_object_id = parent.object_id
inner join sys.objects as child
on   fk.parent_object_id = child.object_id
except
select  ForeignKeyObjectID, ForeignKeyObjectName,
ParentTableID, ParentTableName,
ChildTableID, ChildTableName, @Generation
from  @DependencyTree
end
declare @clearScript as table(ID int primary key identity (0,1), ScriptText nvarchar(max))
insert into @clearScript
select  'alter table [reporter].[' + ChildTableName +
'] drop constraint [' + ForeignKeyObjectName + ']'
from  @DependencyTree
where  ParentTableName in (select Name from @TablesToDelete)
insert into @clearScript
select 'drop table [reporter].[' + Name + ']' from @TablesToDelete
insert into @clearScript
select 'drop function [reporter].[' + Name + ']'
from @FunctionsToDelete
insert into @clearScript
select 'drop procedure [reporter].[' + Name + ']'
from @StoredProceduresToDelete
insert into @clearScript
select 'drop assembly [reporter].[' + Name + ']'
from @AssembliesToDelete
insert into @clearScript
select 'drop view [reporter].[' + Name + ']'
from @ViewsToDelete
if @Debug = 1
begin
select * from @clearScript
end
declare @str nvarchar(max)
declare @ID int;
set @ID  = 0;
declare @MaxID int
select @MaxID = MAX(ID) from @clearScript
print ''
while @ID <= @MaxID
begin
select @str = ScriptText from @clearScript where ID = @ID
if @Emulate = 1
print(@str)
else
exec sp_executesql @statement = @str
set @ID = @ID + 1
end
end
END
  • Can you please post the code for 'RSExecRole.DeleteExtensionModuleDDL? And is the instance's default collation Latin1_General_CI_AS? And what is the default collation of the VeeamOne DB? – Solomon Rutzky Nov 27 '15 at 7:34
  • Hi, the instance default collation is SQL_Latin1_General_CP1_CI_AS and the database default is Latin1_General_CI_AS. As for the object I am searching for that. – Tom Dec 2 '15 at 11:12
  • 1
    OK have posted the code – Tom Dec 2 '15 at 12:08
up vote 2 down vote accepted

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.

  • 1
    superb thanks very much – Tom Dec 3 '15 at 15:50

The MSDN page on Contained Database Collations has some guidance which includes:

  • In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed.

So, your problem is with the catalog collation. As you change to contained, it is changing the database's catalog collation to Latin1_General_100_CI_AS_WS_KS_SC which is the source of your problem.

Perhaps the comments on collation, particulary the CATALOG_DEFAULT may provide you some assistance:

  • The database collation is retained, but is only used as the default collation for user data.
  • A new keyword, CATALOG_DEFAULT, is available in the COLLATE clause. This is used as a shortcut to the current collation of metadata in both contained and non-contained databases.

Crossing Between Contained and Uncontained Contexts

  • As long as a session in a contained database remains contained, it must remain within the database to which it connected. In this case the behavior is very straightforward. But if a session crosses between contained and non-contained contexts, the behavior becomes more complex, since the two sets of rules must be bridged.

And this link ends with the Conclusion:

  • The collation behavior of contained databases differs subtly from that in non-contained databases. This behavior is generally beneficial, providing instance-independence and simplicity. Some users may have issues, particularly when a session accesses both contained and non-contained databases.

Regarding Data Collation Issues:

If you also have to resolve collation problems on the data by using COLLATE DATABASE_DEFAULT. Likely your two databases have the same collation for the data. But if not, you can use the following technique:

select NameValue COLLATE DATABASE_DEFAULT from MyDatabase.Schema.Table
EXCEPT
select NameValue COLLATE DATABASE_DEFAULT from TheirDatabase.Schema.Table

The value of this approach is that you do not need to specify a particular collation, but COLLATE DATABASE_DEFAULT allows you to use the collation of the current database. This would resolve data collation issues.

  • Yes, in my case I am just using the GUI to change the database to partially contained and getting the said error. I am at a loss. – Tom Nov 26 '15 at 14:14
  • @Tom - Added details based on a better understanding of your issue. Most SSMS GUI actions can be used to generate a script. If you use the GUI for that function, generate the script and paste it into a query window. That will give you a chance to examine the commands and perhaps make a needed change to the script. – RLF Nov 26 '15 at 21:26
  • Unfortunately scripting the ALTER Database statement is only a single command when changing to contained. I will try profiler however. – Tom Dec 2 '15 at 12:11

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.