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
'RSExecRole.DeleteExtensionModuleDDL? And is the instance's default collationLatin1_General_CI_AS? And what is the default collation of theVeeamOneDB?