When I try to expand the list of local publications under Replication element in SQ Server Management Studio, I get the following error :

enter image description here

Here is the detailed error log :

===================================

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

As you may have noticed, the error log is pointing to the sys stored procedure sp_MSrepl_enumpublications which contains following code :

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

The error log points to Line 213, contained in the following query in the procedure :

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

I couldn't find the part of the query responsible of this collation conflict, the master db collation is Arabic_CI_AI; I couldn't reproduce the results of this query alone to see exactly what is causing the error.
How can I solve this issue ?

up vote 1 down vote accepted

The issue is most likely this line:

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

as it is a string comparison and neither side is a literal or variable that would then take on the Collation of the column in the Table / View. You can confirm by running the following query:

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

I bet the name column of msdb.dbo.MSdistpublishers has a Collation of Arabic_CI_AS.

It is possible to ALTER TABLE ... ALTER COLUMN to change that column's Collation to Arabic_CI_AI, though I am not sure if that would break anything else. It obviously requires testing.

The other choice is to update that system stored procedure, sp_MSrepl_enumpublications, to change that line to be:

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

P.S. this particular situation is very similar to another in which someone restored msdb from a server that had a different server-level default Collation. That meant that there was a Collation conflict between master and msdb which is usually not possible to have. That question is the following:

Collation conflict ... Could not use dbo.sysdac_instances

  • this is exactly what happened, someone restored an old database from a different server-level collation, thanks srutzky :) Now I have to change the msdb collation, I'm not sure if that's possible in Sql Server 2012, should I post this as a different question..? – mounaim Nov 15 '16 at 11:24

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.