Sql-Server
錯誤 468 MS SQL Server：列舉出版物時無法解決 Arabic_CI_AI 和 Arabic_CI_AS 之間的排序規則衝突
當我嘗試在 SQ Server Management Studio 中的 Replication 元素下展開本地發布列表時，我收到以下錯誤：
這是詳細的錯誤日誌：
=================================== Échec de la récupération de données pour cette demande. (Microsoft.SqlServer.Management.Sdk.Sfc) ------------------------------ Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476 ------------------------------ Emplacement du programme : à Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.RunQuery() à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.Process() à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.get_PropertyNames() à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems) à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter) à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter) à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request) à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent) =================================== Une exception s'est produite lors de l'exécution d'une instruction ou d'un lot Transact-SQL. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Emplacement du programme : à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) à Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteImmediate(String query) à Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm) à Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb) à Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType) à Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result) à Microsoft.SqlServer.Management.Smo.Replication.Publication.GetData(EnumResult erParent) à Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() à Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci) à Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request) à Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) =================================== Impossible de résoudre le conflit de classement entre "Arabic_CI_AI" et "Arabic_CI_AS" dans l'opération equal to. (.Net SqlClient Data Provider) ------------------------------ Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4042&EvtSrc=MSSQLServer&EvtID=468&LinkId=20476 ------------------------------ Nom du serveur : my-server Numéro de l'erreur : 468 Gravité : 16 État : 9 Procédure : sp_MSrepl_enumpublications Numéro de la ligne : 213 ------------------------------ Emplacement du programme : à Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)您可能已經註意到，錯誤日誌指向 sys 儲存過程sp_MSrepl_enumpublications，其中包含以下程式碼：
USE [master] GO /****** Object: StoredProcedure [sys].[sp_MSrepl_enumpublications] Script Date: 11/11/2016 15:13:26 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO -- -- Name: -- sp_MSrepl_enumpublications -- -- Description: -- Enumerate publications on a database -- -- Returns: -- 0 == Failed -- 1 == Succeed -- -- Security: -- public, PAL access for tran publications, dbo check for merge publications -- Requires Certificate signature for catalog access -- -- Notes: -- Used by the UI to generate a list of pubications -- -- Publication type: -- 0 == TRAN -- 1 == SNAPSHOT -- 2 == MERGE -- ALTER PROCEDURE [sys].[sp_MSrepl_enumpublications] ( @reserved bit = 0 -- Set to 1 when used by UI ) AS BEGIN SET NOCOUNT ON DECLARE @retcode int ,@pubid int ,@pubname sysname ,@username sysname ,@OPT_ENABLED_FOR_P2P int ,@category int ,@skippalcheck bit ,@dbname sysname ,@fpublished bit ,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION int -- -- initialize -- select @dbname = db_name() ,@OPT_ENABLED_FOR_P2P = 0x1 ,@username = SUSER_SNAME() ,@fpublished = 0 ,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION = 0x08 -- -- Verify database is published -- Have put the code from fn_MSrepl_ispublished inline for faster processing -- If fn_MSrepl_ispublished() is updated - this code block should be updated as well -- SELECT @category = category FROM master.sys.sysdatabases WHERE name = @dbname IF (@category IS NOT NULL) BEGIN -- We have entry for this db_name in sysdatabases -- Is this database a distributor? IF (@category & 16 = 16) BEGIN -- DB is a distributor - is it used for HREPL publisher? IF OBJECT_ID(N'msdb.dbo.MSdistpublishers') IS NOT NULL BEGIN IF EXISTS ( SELECT name FROM msdb.dbo.MSdistpublishers WHERE distribution_db = @dbname AND publisher_type != N'MSSQLSERVER' ) BEGIN SELECT @fpublished = 1 END END END ELSE BEGIN -- Check if this database is a transactional or merge publisher IF (@category & 1 = 1 OR @category & 4 = 4) BEGIN SELECT @fpublished = 1 END END END -- @category not null -- -- If the database is not published - return -- IF (@fpublished = 0) BEGIN RETURN (0) END -- -- Security Check. -- Skip PAL check if DBO -- For PAL check - Part of the query to gather the list of publications uses security context -- select @skippalcheck = case when (is_member ('db_owner') = 1 OR is_srvrolemember('sysadmin') = 1) then 1 else 0 end if (@skippalcheck = 0) begin DECLARE @accessiblepubs TABLE ( pubid int ) end -- -- Create local temp table if needed -- if (@reserved = 0) begin create TABLE #tmp_publications ( publisher sysname not null, dbname sysname not null, publication sysname not null, publisher_type sysname not null, publication_type int not null, description nvarchar(255) null, allow_queued bit default 0 NOT NULL, enabled_for_p2p bit default 0 NOT NULL, enabled_for_p2pconflictdetection bit default 0 NOT NULL ) end -- -- Get snapshot or transactional publications -- IF object_id(N'dbo.syspublications') IS NOT NULL BEGIN if (@skippalcheck = 0) begin -- Catalog accessible pub ids DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR SELECT pubid, name FROM dbo.syspublications OPEN #hC FETCH #hC INTO @pubid, @pubname WHILE (@@fetch_status <> -1) BEGIN EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @pubname, @raise_fatal_error = 0, @given_login = @username IF (@retcode = 0 AND @@error = 0) BEGIN INSERT INTO @accessiblepubs values(@pubid) END FETCH #hC INTO @pubid, @pubname END CLOSE #hC DEALLOCATE #hC end -- if (@skippalcheck = 0) -- -- Determine if distribution db is being cataloged -- IF (@category & 16 != 16) BEGIN -- SQL Server publication db INSERT INTO #tmp_publications ( publisher, dbname, publication, publisher_type, publication_type, description, allow_queued, enabled_for_p2p, enabled_for_p2pconflictdetection ) SELECT publishingservername(), @dbname, name, N'MSSQLSERVER', repl_freq, description, allow_queued_tran, (options & @OPT_ENABLED_FOR_P2P), (options & @OPT_ENABLED_FOR_P2PCONFLICTDETECTION) FROM dbo.syspublications WHERE @skippalcheck = 1 OR (pubid IN (SELECT pubid FROM @accessiblepubs)) END ELSE BEGIN -- Distribution db - Heterogeneous publications INSERT INTO #tmp_publications ( publisher, dbname, publication, publisher_type, publication_type, description, allow_queued, enabled_for_p2p ) SELECT ss.srvname, @dbname, -- distribution db name for enumerator to work sp.name, msd.publisher_type, sp.repl_freq, sp.description, sp.allow_queued_tran, (sp.options & @OPT_ENABLED_FOR_P2P) FROM dbo.syspublications sp join dbo.MSpublications msp on sp.pubid = msp.publication_id join master.dbo.sysservers ss on msp.publisher_id = ss.srvid join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname WHERE @skippalcheck = 1 OR (pubid IN (SELECT pubid FROM @accessiblepubs)) END END -- IF object_id(N'dbo.syspublications') IS NOT NULL -- -- Get merge publications -- IF object_id(N'dbo.sysmergepublications') IS NOT NULL BEGIN INSERT INTO #tmp_publications ( publisher, dbname, publication, publisher_type, publication_type, description ) SELECT publisher, publisher_db, name, N'MSSQLSERVER', 2, description FROM dbo.sysmergepublications WHERE (@skippalcheck = 1 OR {fn ISPALUSER(pubid)} = 1) and publisher_db = @dbname and UPPER(publisher) = UPPER(publishingservername()) END -- object_id(N'dbo.sysmergepublications') IS NOT NULL -- -- Return result set if we created local table -- if (@reserved = 0) begin SELECT * FROM #tmp_publications ORDER BY publisher, dbname, publication end -- -- all done -- RETURN (0) END錯誤日誌指向第 213 行，包含在過程中的以下查詢中：
BEGIN -- Distribution db - Heterogeneous publications INSERT INTO #tmp_publications ( publisher, dbname, publication, publisher_type, publication_type, description, allow_queued, enabled_for_p2p ) SELECT ss.srvname, @dbname, -- distribution db name for enumerator to work sp.name, msd.publisher_type, sp.repl_freq, sp.description, sp.allow_queued_tran, (sp.options & @OPT_ENABLED_FOR_P2P) FROM dbo.syspublications sp join dbo.MSpublications msp on sp.pubid = msp.publication_id join master.dbo.sysservers ss on msp.publisher_id = ss.srvid join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname WHERE @skippalcheck = 1 OR (pubid IN (SELECT pubid FROM @accessiblepubs)) END我找不到負責此排序規則衝突的查詢部分，主數據庫排序規則是Arabic_CI_AI；我無法單獨重現此查詢的結果以確切了解導致錯誤的原因。
我該如何解決這個問題？
問題很可能是這一行：
join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname因為它是一個字元串比較，並且任何一方都不是文字或變數，它們隨後將採用表/視圖中列的排序規則。您可以通過執行以下查詢來確認：
SELECT ss.srvname FROM master.dbo.sysservers ss INNER JOIN msdb.dbo.MSdistpublishers msd ON msd.name = ss.srvname;我敢打賭 的
name列msdb.dbo.MSdistpublishers有一個排序規則Arabic_CI_AS。可以
ALTER TABLE ... ALTER COLUMN將該列的排序規則更改為Arabic_CI_AI，但我不確定這是否會破壞其他任何內容。這顯然需要測試。另一種選擇是更新該系統儲存過程 ,
sp_MSrepl_enumpublications將該行更改為：join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname COLLATE Arabic_CI_AIPS這種特殊情況與另一種情況非常相似，其中有人
msdb從具有不同伺服器級預設排序規則的伺服器恢復。這意味著兩者之間存在排序規則衝突，master而msdb這通常是不可能發生的。這個問題如下：