错误468 MS Server :在枚举发布时无法解决Arabic_CI_AI和Arabic_CI_AS之间的排序冲突

Question

当我试图在中的复制元素下展开本地发布列表时，我会得到以下错误：

下面是详细的错误日志：

===================================
    
    É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

我找不到查询中负责此排序规则冲突的部分，主db排序规则是Arabic_CI_AI；我无法单独再现该查询的结果，以查看导致错误的确切原因。

我怎样才能解决这个问题？

Answer 1

最有可能的问题是这句话：

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_AI

P.S.这种特殊情况与另一种情况非常相似，在另一种情况下，有人从具有不同服务器级别默认排序规则的服务器中恢复msdb。这意味着master和msdb之间存在着排序冲突，这通常是不可能的。这一问题如下：

整理冲突...无法使用dbo.sysdac_实例