Início / dba / Perguntas / 122186
Accepted
Tom
Asked: 2015-11-27 05:16:07 +0800 CST

Erro de agrupamento de banco de dados contido

  • 772

Ao alterar um banco de dados para parcialmente contido, recebo o seguinte erro:

Não é possível resolver o conflito de agrupamento entre "Latin1_General_CI_AS" e "Latin1_General_100_CI_AS_KS_WS_SC" na operação EXCEPT.

Foram encontrados erros no procedimento 'RSExecRole.DeleteExtensionModuleDDL' durante a compilação do objeto >. A opção de contenção do banco de dados 'VeeamOne' foi alterada ou este objeto estava presente no modelo db e o usuário tentou criar um novo banco de dados contido. Falha na instrução ALTER DATABASE. A opção de contenção do banco de dados 'VeeamOne' não pôde ser alterada porque foram encontrados erros de compilação durante a validação dos módulos SQL. Veja os erros anteriores. Falha na instrução ALTER DATABASE. (Provedor de Dados .Net SqlClient)

Acho que o objeto que está relatando é do SSRS. No entanto, o banco de dados no qual estou alterando o agrupamento é um aplicativo completamente separado.

Alguém tem alguma sugestão de como resolver isso?

==================================================== ======================= OK, este é o código para o proc, não tenho certeza do que faz com que ele não seja capaz de ser contido

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
sql-server sql-server-2012

3 respostas

  1. Best Answer

    O problema que você está vendo é um conflito entre o agrupamento dos metadados nas exibições do sistema - sys.foreign_keyse sys.objects- e a variável da tabela @DependencyTree.

    Conforme apontado na resposta de @RLF , o agrupamento dos metadados do banco de dados muda de DATABASE_DEFAULT (no seu caso Latin1_General_CI_AS) para CATALOG_DEFAULT (sempre Latin1_General_100_CI_AS_WS_KS_SC) ao alterar o banco de dados para ser "contido". Isso afeta os namecampos que estão sendo retornados nesta consulta:

    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

    Os campos fk.name, parent.namee child.namesão todos agrupados inicialmente como Latin1_General_CI_AS, mas depois mudam para Latin1_General_100_CI_AS_WS_KS_SCquando você ALTER o banco de dados para torná-lo "contido".

    O erro está sendo lançado porque os campos de string em ambas as partes EXCEPTprecisam ter agrupamentos correspondentes. Mas a outra parte EXCEPTestá usando a variável de tabela que é definida como:

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

    Nenhum agrupamento é especificado para os NVARCHAR(MAX)campos (que tecnicamente devem ser declarados como sysname-- sempre todos em minúsculas para aquele -- já que esse é o tipo de dados das Visualizações do sistema de origem de sys.objectse sys.foreign_keys). Embora não seja mencionado na página MSDN Contained Database Collations Table , ao contrário das tabelas temporárias, as variáveis ​​de tabela obtêm seu agrupamento padrão do banco de dados, não de tempdb(é por isso que você não viu esse erro no passado, pois seu tempdbagrupamento deve ser SQL_Latin1_General_CP1_CI_ASdesde esse é o agrupamento de instâncias; você teria obtido esse erro antes se esta tabela fosse uma tabela temporária). Portanto, o agrupamento usado para os campos ForeignKeyObjectName, ParentTableNamee foiChildTableNameLatin1_General_CI_ASe ainda será o mesmo agrupamento após o banco de dados ser "contido".

    Alterar essa declaração de variável de tabela para o seguinte deve resolver esse problema:

    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
);

    O uso COLLATE CATALOG_DEFAULTfuncionará com bancos de dados quando eles não estiverem contidos e quando forem alterados para serem contidos, pois CATALOG_DEFAULTresolve o padrão do banco de dados em bancos de dados não contidos. Outra maneira de declarar esse comportamento é que, como os metadados do banco de dados são agrupados CATALOG_DEFAULTem qualquer estado do banco de dados, eles funcionarão na variável de tabela (e nas tabelas temporárias) em qualquer estado do banco de dados.

    • 6

  2. A página do MSDN em Contained Database Collations tem algumas orientações que incluem:

    • Em um banco de dados contido, o agrupamento de catálogo Latin1_General_100_CI_AS_WS_KS_SC. Esse agrupamento é o mesmo para todos os bancos de dados contidos em todas as instâncias do SQL Server e não pode ser alterado .

    Portanto, seu problema é com o agrupamento de catálogos . À medida que você muda para contido, ele altera o agrupamento do catálogo do banco de dados para Latin1_General_100_CI_AS_WS_KS_SC, que é a origem do seu problema.

    Talvez os comentários sobre o agrupamento, particularmente o CATALOG_DEFAULT , possam fornecer alguma ajuda:

    • A ordenação do banco de dados é mantida , mas é usada apenas como ordenação padrão para dados do usuário .
    • Uma nova palavra-chave, CATALOG_DEFAULT , está disponível na cláusula COLLATE. Isso é usado como um atalho para o agrupamento atual de metadados em bancos de dados contidos e não contidos .

    Cruzamento entre contextos contidos e não contidos

    • Enquanto uma sessão em um banco de dados contido permanecer contida, ela deverá permanecer dentro do banco de dados ao qual está conectada. Neste caso, o comportamento é muito simples. Mas se uma sessão cruza entre contextos contidos e não contidos, o comportamento torna-se mais complexo, pois os dois conjuntos de regras devem ser interligados.

    E este link termina com a Conclusão:

    • O comportamento de agrupamento de bancos de dados contidos difere sutilmente daquele em bancos de dados não contidos. Esse comportamento geralmente é benéfico, fornecendo independência de instância e simplicidade. Alguns usuários podem ter problemas, principalmente quando uma sessão acessa bancos de dados contidos e não contidos.

    Com relação aos problemas de agrupamento de dados :

    Se você também tiver que resolver problemas de agrupamento nos dados usando COLLATE DATABASE_DEFAULT. Provavelmente seus dois bancos de dados têm o mesmo agrupamento para os dados. Mas se não, você pode usar a seguinte técnica:

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

    O valor dessa abordagem é que você não precisa especificar um agrupamento específico, mas COLLATE DATABASE_DEFAULTpermite usar o agrupamento do banco de dados atual. Isso resolveria problemas de agrupamento de dados .

    • 5

  3. Isto é para mostrar uma solução alternativa que implementei - se suas mãos estão atadas e você não pode alterar as tabelas de origem que estão causando um erro de comparação, isso funciona: Insira os valores que você precisa comparar em uma tabela temporária que tenha o atributo COLLATE CATALOG_DEFAULT conforme mencionado acima .

     /* This is an interesting problem I ran across while trying to do some work in a collated database. 
The SQLLogin column on the LoginsReference table is nvarchar(50) (yes, it should be sysname)
When trying to see if [name] from the sys.sysusers table existed in the LoginsReference table, I got the following error:

--Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.

My temporary solution is to leverage implicit conversion to insert the SQLLogins FROM LoginsReference into a temp table that is properly collated, and use it for the comparison operator.
Long term solution is to properly collate things in tables.  However, there is a lot of dynamic SQL involved and I'm worried there will be many more issues with other comparisons made in stored procedures elsewhere.  We'll see.
*/
--this is a script to clean out users created in testing and also reset all tables involved in testing.
USE [_Contained Database]
DECLARE @UUID smallint
DECLARE @SQL nvarchar(max)
DECLARE @loginname nvarchar (20)
DECLARE @stop bit
IF OBJECT_ID('tempdb..#usernames') is NOT NULL
DROP TABLE #usernames
CREATE TABLE #usernames
([sqlLogin] nvarchar (50) COLLATE CATALOG_DEFAULT)
INSERT INTO #userNames
SELECT [sqlLogin] FROM [dbo].[LoginsReference]


WHILE EXISTS (SELECT TOP 1 [UID] FROM sys.sysusers WHERE [uid] BETWEEN 5 and 16000 and [name] in (SELECT [sqlLogin] FROM #usernames))
BEGIN
    SELECT TOP 1 @loginname = [name] FROM sys.sysusers WHERE [uid] BETWEEN 5 and 16000 and [name] in (SELECT [sqlLogin] FROM #usernames)
    SELECT @SQL = 'DROP USER ' + @loginname
    exec sp_executesql @sql
END
TRUNCATE TABLE [dbo].[LoginRoles]
TRUNCATE TABLE [dbo].[LoginsReference]

    DE: https://github.com/scorellis/SQLTutorials/blob/master/COLLATE_Comparison_problem

    • 0

relate perguntas