Início / dba / Perguntas / 154982
Accepted
mounaim
Asked: 2016-11-12 08:13:53 +0800 CST

Erro 468 MS SQL Server: não foi possível resolver o conflito de agrupamento entre Arabic_CI_AI e Arabic_CI_AS ao enumerar publicações

  • 772

Quando tento expandir a lista de publicações locais no elemento Replication no SQ Server Management Studio, recebo o seguinte erro:

insira a descrição da imagem aqui

Aqui está o log de erro detalhado:

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

Como você deve ter notado, o log de erros está apontando para o procedimento armazenado sys sp_MSrepl_enumpublications que contém o seguinte código:

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

O log de erros aponta para a Linha 213, contida na seguinte consulta no procedimento:

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

Não consegui encontrar a parte da consulta responsável por este conflito de collation, o master db collation é Arabic_CI_AI ; Não consegui reproduzir os resultados desta consulta sozinho para ver exatamente o que está causando o erro.
Como posso resolver este problema?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    O problema provavelmente é esta linha:

    join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname

    pois é uma comparação de string e nenhum dos lados é um literal ou variável que assumiria o agrupamento da coluna na tabela/exibição. Você pode confirmar executando a seguinte consulta:

    SELECT ss.srvname
FROM master.dbo.sysservers ss
INNER JOIN msdb.dbo.MSdistpublishers msd
        ON msd.name = ss.srvname;

    Aposto que a namecoluna de msdb.dbo.MSdistpublisherstem um Collation de Arabic_CI_AS.

    É possível ALTER TABLE ... ALTER COLUMNalterar o Collation dessa coluna para Arabic_CI_AI, embora eu não tenha certeza se isso quebraria qualquer outra coisa. Obviamente requer testes.

    A outra opção é atualizar esse procedimento armazenado do sistema, sp_MSrepl_enumpublications, para alterar essa linha para:

    join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname COLLATE Arabic_CI_AI

    PS, esta situação em particular é muito semelhante a outra em que alguém restaurou msdbde um servidor que tinha um Collation padrão de nível de servidor diferente. Isso significava que havia um conflito de Collation entre mastere msdbque geralmente não é possível ter. Essa pergunta é a seguinte:

    Conflito de agrupamento... Não foi possível usar dbo.sysdac_instances

    • 1

relate perguntas