none
Unable to Drop Subscription

All replies

  • do this

     

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

     

    GO

    N'FrameShop', @ignore_distributor=1


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, March 31, 2011 4:30 PM
    Moderator
  • 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


    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:

    EXEC

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

     

    GO

     

    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: http://msdn.microsoft.com/en-us/library/ms188411.aspx

    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;
    GO
    -Eric Niemiec
    Sunday, January 27, 2013 2:33 PM