none
Replication preventing drop of linked server not used in replication.

    Question

  • An hour ago I manually created a linked server of type SQL Server for temporary admin use. The linked server exists on a server that does have replication subscriptions and publications which were created several days ago.

    I just tried to remove the linked server and could not:

    EXEC master.dbo.sp_dropserver @server=N'myserver\myinstance', @droplogins='droplogins'

    Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 31 [Batch Start Line 39]
    Cannot drop server 'myserver\myinstance' because it is used as a Publisher in replication.


    No, it isnt:

    select is_publisher, is_subscriber, is_distributor from sys.servers where name = 'myserver\myinstance'

    is_publisher is_subscriber is_distributor
    ------------ ------------- --------------
    0            0             0

    Bug? How do I get rid of this now?



    • Edited by allmhuran Friday, November 10, 2017 4:31 AM
    Friday, November 10, 2017 4:30 AM

All replies

  • Digging into the cause of the error, the check being done by sp_MSrepl_check_server is as follows:

    -- Check to see if the server is a dist publisher
            if object_id('msdb.dbo.MSdistpublishers') is not null
            begin
                if exists (select * from msdb.dbo.MSdistpublishers where
                    UPPER(name) = UPPER(@srvname) collate database_default)
                begin
                    raiserror(20582, 16, -1, @srvname)
                    return (1)
                end
            end

    So as a hack solution (since nothing else worked) I simply deleted the row from MSdistpublishers. No doubt this will leave things in some kind of fractured state, but clearly that was already the case, and at least this gets me past the blocking error.

    If anyone knows what the solution is "meant" to be, I'm still interested.



    • Edited by allmhuran Friday, November 10, 2017 5:53 AM
    Friday, November 10, 2017 5:52 AM
  • Hi allmhuran,

    >>If anyone knows what the solution is "meant" to be, I'm still interested.

    Your solution should be fine. Based on my research, it seems in most cases, the issue is related to an improper server rename, that could happen years ago before you encounter this error. Is that the case?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 5:27 AM
    Moderator
  •  This worked for me as well. I was able to drop the linked server after deleting the row from table MSDistpublishers in <g class="gr_ gr_76 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="76" id="76"><g class="gr_ gr_88 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="88" id="88">msdb</g></g> database. Thank you very much for the solution.
    Thursday, November 15, 2018 3:50 PM