Unable to Drop Subscription RRS feed

All replies

  • do this


     sp_dropmergesubscription @publication = N'CatalogPublication', @subscriber = N'0970HR\SQLExpress', @subscriber_db =



    N'FrameShop', @ignore_distributor=1

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?
    Thursday, March 31, 2011 4:30 PM
  • Hey Cotter,


    Why i didnt got this idea, even i know this script...

    Anyway thanks alot it worked i was able to cleanup the subscription entry now.



    Regards Jak
    Thursday, March 31, 2011 4:57 PM
  • I'm having similar issue.  Cannot delete subscriber due to a hard drive crash.  I've tried all methods listed above and received same errors noted above.

    As last resort I've tried running the following code:


    sp_dropmergesubscription @publication = N'Audit_Database_Publication', @subscriber = N'LHE-TABLET1\SQLEXPRESS668', @subscriber_db =




    N'Audit_Database', @ignore_distributor = 1

    I receive the following errors:

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near '='.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near 'Audit_Database'.

    The publication name is 'Audit_Database_Publication'

    The subscriber is 'LHE-TABLET1\SQLEXPRESS668'

    The publication database is 'Audit_Database'

    My main issue is that the replication was not properly setup by the original administrator; we are trying to remove the replication, handle all of the prerequisite issues with the associated tables and then recreate the replication process.

    I am currently stuck at trying to remove subscriptions that no longer exist; due to hardware failures on remote laptops.

    Wednesday, June 20, 2012 5:21 PM
  • For future use, I was having the same problems described above - but I also had database snapshots on the database that I was trying to remove replication from but that was not mentioned anywhere in the error messages.

    Once I removed my database snapshots, the following script cleans things up well:

    Example came from here:

    DECLARE @distributionDB AS sysname;
    DECLARE @publisher AS sysname;
    DECLARE @publicationDB as sysname;
    SET @distributionDB = N'distribution';
    SET @publisher = N'S-ESN-SQL1\SQL1';
    SET @publicationDB = N'AdventureWorks2008R2';
    -- Disable the publication database.
    USE [AdventureWorks2008R2]
    EXEC sp_removedbreplication @publicationDB;
    -- Remove the registration of the local Publisher at the Distributor.
    USE master
    EXEC sp_dropdistpublisher @publisher;
    -- Delete the distribution database.
    EXEC sp_dropdistributiondb @distributionDB;
    -- Remove the local server as a Distributor.
    EXEC sp_dropdistributor;
    -Eric Niemiec
    Sunday, January 27, 2013 2:33 PM
  • Dozens of articles later and Eric's post was the one that did the trick for me!
    Sunday, November 8, 2015 6:25 PM