none
Cannot Remove Local Publication RRS feed

  • Question

  • Hi Folks,

    I have two servers which both have IIS and SQL 2014 installed. One hosts our production website, and the other is a DR copy. The server names are WEB and DR-WEB, respectively.

    I tried setting up SQL replication for the CMS DB from WEB to DR-WEB such that WEB is the publisher and DR-WEB is the distributor and subscriber. For one reason or another it wasn't working properly, and I decided it would probably work better as a push subscription, so I wanted to make WEB the distributor instead.

    I set about removing the configuration I had created to try again, but (of course) have run into some issues since I probably didn't do it correctly. I was able to drop the distributor DB on DR-Web and remove the subscription object there, but I can't seem to remove the publication object on WEB.

    If I rclick on the publication object on WEB and choose delete, I get an error saying:

    "Could not connect to server 'DR-WEB' because 'WEB' is not defined as a remote server. Verify that you have specified the correct server name. (Microsoft SQL Server, error:18482)"

    This error is kind of throwing me since WEB is the local server, and shouldn't be defined as remote. If I rclick on the publication object and choose properties -> Publication Access List, I get the same error. I've Googled this to death and have come up with various SQL Transact statements, but I'm not a DBA and it's a bit scary on a production server for someone who doesn't have a solid understanding of what they're doing.

    Can anyone tell me how to get rid of this publication so I can try again? Thanks muchly,

    ianc



    • Edited by ianc3 Thursday, November 3, 2016 9:32 PM
    Thursday, November 3, 2016 9:31 PM

Answers

  • do this in the publication database

    exec sp_droppublication 'PublicationName', @ignore_distributor=1

    • Marked as answer by ianc3 Thursday, November 3, 2016 9:41 PM
    Thursday, November 3, 2016 9:35 PM
    Moderator

All replies

  • do this in the publication database

    exec sp_droppublication 'PublicationName', @ignore_distributor=1

    • Marked as answer by ianc3 Thursday, November 3, 2016 9:41 PM
    Thursday, November 3, 2016 9:35 PM
    Moderator
  • Awesome, that worked a treat!

    I've been battling that for hours and you solved it in three min. Kudos to you Hilary and thanks a mill!

    ianc

    P.S. I think I need a little additional help though. I tried recreating the publication object on WEB again and walked through the wizard, but after I had finished it and clicked OK, the wizard failed on the first step of creating the publication with the same error:

    "Could not connect to server 'DR-WEB' because 'WEB' is not defined as a remote server. Verify that you have specified the correct server name. (Microsoft SQL Server, error:18482)"

    Do I need to remove metadata about the previous publication job first somehow? Thanks for any additional help...

    ianc


    • Edited by ianc3 Thursday, November 3, 2016 10:05 PM
    Thursday, November 3, 2016 9:41 PM
  • OK, got this sorted I think. Check under Server Objects -> Linked Servers and found DR-WEB there, so deleted it, then tried again to create the publication. Still no go with same error.

    Also found a 'repl_distributor' object there which I could not delete. Did a bit more searching and found this:

    https://sqlaj.wordpress.com/2012/03/23/cannot-drop-server-repl_distributor-because-it-is-used-as-a-distributor/

    So tried this:

    EXEC master.sys.sp_serveroption

    @server = 'repl_distributor'

    , @optname = 'dist'

    . @optvalue = 'false'

    Bam, that killed the repl_distributor object and I'm now able to create a publication again!

    HTH,

    ianc

    Thursday, November 3, 2016 10:35 PM