Sql-Server

包含的數據庫歸類錯誤

  • January 27, 2019

將數據庫更改為部分包含時，出現以下錯誤：

無法解決 EXCEPT 操作中“Latin1_General_CI_AS”和“Latin1_General_100_CI_AS_KS_WS_SC”之間的排序規則衝突。

在編譯 > 對象期間，過程“RSExecRole.DeleteExtensionModuleDDL”中遇到錯誤。數據庫“VeeamOne”的包含選項已更改，或者此對象存在於模型 db 中，並且使用者嘗試創建新的包含數據庫。ALTER DATABASE 語句失敗。無法更改數據庫“VeeamOne”的包含選項，因為在驗證 SQL 模組期間遇到編譯錯誤。請參閱以前的錯誤。ALTER DATABASE 語句失敗。（.Net SqlClient 數據提供者）

我認為這是報告的對象來自 SSRS。但是，我正在更改排序規則的數據庫是一個完全獨立的應用程序。

有人對如何解決這個問題有任何建議嗎？

==================================================== ======================= 好的，這是proc的程式碼，不知道它是怎麼回事導致它無法被包含

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

您看到的問題是系統視圖中元數據的排序 -sys.foreign_keys和sys.objects- 與表變數之間的衝突@DependencyTree。

正如@RLF 的回答中所指出的，當將數據庫更改為“包含”時，數據庫元數據的排序規則會從 DATABASE_DEFAULT（在您的情況下Latin1_General_CI_AS）更改為 CATALOG_DEFAULT（始終）。Latin1_General_100_CI_AS_WS_KS_SC這會影響name此查詢中返回的欄位：

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

、和欄位最初都被整理為，fk.name但隨後更改為當您更改數據庫以使其“包含”時。parent.name``child.name``Latin1_General_CI_AS``Latin1_General_100_CI_AS_WS_KS_SC

拋出錯誤是因為兩個部分的字元串欄位都EXCEPT需要匹配的排序規則。但另一部分EXCEPT是使用定義為的表變數：

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

沒有為這些NVARCHAR(MAX)欄位指定排序規則（從技術上講，它應該聲明為sysname——對於那個總是小寫——因為那是源系統視圖的數據類型sys.objects和sys.foreign_keys）。雖然在Contained Database Collat​​ions Table MSDN 頁面中沒有提到它，但與臨時表不同，表變數從數據庫而不是從數據庫獲取其預設排序規則tempdb（這就是為什麼您過去沒有看到此錯誤，因為您的tempdb排序規則應該SQL_Latin1_General_CP1_CI_AS是那是實例排序規則；如果此表是臨時表，您之前會收到此錯誤）。所以用於 , 和 欄位的ForeignKeyObjectName排序ParentTableName規則ChildTableName是Latin1_General_CI_AS並且在數據庫被“包含”時仍然是相同的排序規則。

將該表變數聲明更改為以下內容應該可以解決此問題：

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

使用COLLATE CATALOG_DEFAULT將在數據庫不包含以及將它們更改為包含時適用於數據庫，因為CATALOG_DEFAULT解析為非包含數據庫中的數據庫預設值。說明這種行為的另一種方式是，由於數據庫元數據是在數據庫的任一狀態下進行整理CATALOG_DEFAULT的，因此它將在數據庫的任一狀態下的表變數（和臨時表）中工作。

引用自：https://dba.stackexchange.com/questions/122186

相關問答

Sql-Server

SQL Server：無法解決“Latin1_General_CI_AS”和“之間的排序規則衝突”Garbag和G一種rb一種G和garbage'

  • February 25, 2020
檢視問答
Sql-Server

sp_Blitz 因整理錯誤而失敗

  • February 22, 2020
檢視問答
Sql-Server

ORDER BY 和比較混合的字母和數字字元串

  • May 14, 2019
檢視問答
Sql-Server

在 SQL Server 2012 中比較兩個大型結果集的最有效方法是什麼

  • November 9, 2018
檢視問答
Sql-Server

EXCEPT 運算符查詢以區分新行和更改的行

  • October 30, 2018
檢視問答
Sql-Server

無法解決“SQL_Latin1_General_CP1_CI_AS”和“Latin1_General_CI_AI”之間的排序規則衝突

  • August 3, 2018
檢視問答