none
unable to disable publishing and distribution RRS feed

  • Question

  • I need to cleanup a partial setup of replication that failed.  When I try to disable the replication I get the following message.  Can anyone tell me how to remove the distribution database.

     

     

     

     

    TITLE: Microsoft.SqlServer.ConnectionInfo
    ------------------------------

    SQL Server could not disable publishing and distribution on 'ASH-DB'.

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Invalid object name 'dbo.syssubscriptions'.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
    Changed database context to 'master'. (Microsoft SQL Server, Error: 208)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Monday, November 19, 2007 9:48 PM

Answers

  • The first thing to do is to unpublish the databases.

    Locate the published database and unpublish them by using

    sp_replicationdboption 'databasename','publish','false'

    or

    sp_replicationdboption 'databasename','merge publish','false'

    It appears like your meta data is inconsistent. You may need to create the syspublications table to be able to disable the published database.

    Here is the schema of this table.

    CREATE TABLE [dbo].[syspublications](
        [description] [nvarchar](255) NULL,
        [name] [sysname] NOT NULL,
        [pubid] [int] IDENTITY(1,1) NOT NULL,
        [repl_freq] [tinyint] NOT NULL,
        [status] [tinyint] NOT NULL,
        [sync_method] [tinyint] NOT NULL,
        [snapshot_jobid] [binary](16) NULL,
        [independent_agent] [bit] NOT NULL,
        [immediate_sync] [bit] NOT NULL,
        [enabled_for_internet] [bit] NOT NULL,
        [allow_push] [bit] NOT NULL,
        [allow_pull] [bit] NOT NULL,
        [allow_anonymous] [bit] NOT NULL,
        [immediate_sync_ready] [bit] NOT NULL,
        [allow_sync_tran] [bit] NOT NULL,
        [autogen_sync_procs] [bit] NOT NULL,
        [retention] [int] NULL,
        [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
        [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
        [alt_snapshot_folder] [nvarchar](255) NULL,
        [pre_snapshot_script] [nvarchar](255) NULL,
        [post_snapshot_script] [nvarchar](255) NULL,
        [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
        [ftp_address] [sysname] NULL,
        [ftp_port] [int] NOT NULL DEFAULT ((21)),
        [ftp_subdirectory] [nvarchar](255) NULL,
        [ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
        [ftp_password] [nvarchar](524) NULL,
        [allow_dts] [bit] NOT NULL DEFAULT ((0)),
        [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
        [centralized_conflicts] [bit] NULL,
        [conflict_retention] [int] NULL,
        [conflict_policy] [int] NULL,
        [queue_type] [int] NULL,
        [ad_guidname] [sysname] NULL,
        [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
        [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
        [min_autonosync_lsn] [binary](10) NULL,
        [replicate_ddl] [int] NULL DEFAULT ((1)),
        [options] [int] NOT NULL DEFAULT ((0))
    ) ON [PRIMARY]

    Tuesday, November 20, 2007 4:28 AM
    Moderator

All replies

  • The first thing to do is to unpublish the databases.

    Locate the published database and unpublish them by using

    sp_replicationdboption 'databasename','publish','false'

    or

    sp_replicationdboption 'databasename','merge publish','false'

    It appears like your meta data is inconsistent. You may need to create the syspublications table to be able to disable the published database.

    Here is the schema of this table.

    CREATE TABLE [dbo].[syspublications](
        [description] [nvarchar](255) NULL,
        [name] [sysname] NOT NULL,
        [pubid] [int] IDENTITY(1,1) NOT NULL,
        [repl_freq] [tinyint] NOT NULL,
        [status] [tinyint] NOT NULL,
        [sync_method] [tinyint] NOT NULL,
        [snapshot_jobid] [binary](16) NULL,
        [independent_agent] [bit] NOT NULL,
        [immediate_sync] [bit] NOT NULL,
        [enabled_for_internet] [bit] NOT NULL,
        [allow_push] [bit] NOT NULL,
        [allow_pull] [bit] NOT NULL,
        [allow_anonymous] [bit] NOT NULL,
        [immediate_sync_ready] [bit] NOT NULL,
        [allow_sync_tran] [bit] NOT NULL,
        [autogen_sync_procs] [bit] NOT NULL,
        [retention] [int] NULL,
        [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
        [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
        [alt_snapshot_folder] [nvarchar](255) NULL,
        [pre_snapshot_script] [nvarchar](255) NULL,
        [post_snapshot_script] [nvarchar](255) NULL,
        [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
        [ftp_address] [sysname] NULL,
        [ftp_port] [int] NOT NULL DEFAULT ((21)),
        [ftp_subdirectory] [nvarchar](255) NULL,
        [ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
        [ftp_password] [nvarchar](524) NULL,
        [allow_dts] [bit] NOT NULL DEFAULT ((0)),
        [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
        [centralized_conflicts] [bit] NULL,
        [conflict_retention] [int] NULL,
        [conflict_policy] [int] NULL,
        [queue_type] [int] NULL,
        [ad_guidname] [sysname] NULL,
        [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
        [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
        [min_autonosync_lsn] [binary](10) NULL,
        [replicate_ddl] [int] NULL DEFAULT ((1)),
        [options] [int] NOT NULL DEFAULT ((0))
    ) ON [PRIMARY]

    Tuesday, November 20, 2007 4:28 AM
    Moderator
  • Hi,

    If the previous answer didn't help you could try to create a new publication using the same publisher,

    and try to disable publishing and distribution after this step.

     

    Tuesday, December 11, 2007 2:38 PM
  • I'm not an SQL-guy, so bare with me.

     

    I am having this exact problem.  When I run the sp_replicationdboption commands above, they run successfully.  When I then try to use the wizard to disable replication it fails, saying that somethings still need to be committed to the database and something about ROLLBACKS.

     

    The DISTRIBUTION system database is not there (e.g. it has been removed), but SQL thinks it is still in-use.  When I try to create a new publication, I then get an error about "invalid object.  dbo.syspublications is missing".  Where do I run your create-table statement above?  On MASTER?  Or, recreate DISTRIBUTION and then run it?

     

    This is weird, I have seen a number of posting about this very problem, all the way back to SQL 7.0...same problem and hardly anyone has a clear-cut resolution for it.  I can't believe MS does not have a good solution for this or better error-handling yet...and we are now on the heels of SQL Server 2008!!

     

    Go figure!!

    Tuesday, February 5, 2008 8:38 PM
  • Hello Hillary, et el,

    I have the same problems when I tried to disable Publishing and Distribution through wizard.

    My transaction database replication environment:

    --- Publishers (SQL Server 2000 SP3)
    --- A remote distributor (SQL Server 2008 SP1)
    --- A Subscriber (SQL Server 2008 SP1)

    The first error complained "Could not connect to server "Distributor server" because is not defined as a remote server" then I clicked OK.
    The second error complained "Invalid object name 'syssubscriptions' Change database context to master (Microsoft SQL Server, Error: 208)

    The remote distributor all ready dropped and remoted.

    Can someone help and provide me a solution?


    Thanks in advance. 
    TJ



    None
    Thursday, December 17, 2009 8:28 PM
  • try to disable replication using the sp_droppublication @publication='all',@ignore_distributor=1

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html 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
    Saturday, December 19, 2009 1:26 PM
    Moderator
  • Hi,

    I also have the same issue and tried all the suggestion too.

    But nothing worked. Again same error... 

    Any further suggestion ?
    Sunday, January 3, 2010 2:36 PM
  • what error message do you get when you issue this command?

    sp_droppublication @publication='all',@ignore_distributor=1

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html 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
    Monday, January 4, 2010 11:53 AM
    Moderator
  • Hi,

    I got the same problem as well, when I issue the command above I got the message said the database has not start the publication yet.

    Can you provide further suggestions?

    Thanks

    Wednesday, June 30, 2010 8:31 AM
  • pherciaC,

    run this: exec sp_dropdistributor @no_checks =1

    then re setup your distribution database and publishers.

     

     

    Wednesday, June 30, 2010 6:46 PM
  • NO, NOW WE ARE PAST SQL SERVER 2019 AND YET NO CLEAR CUT SOLUTION TO THIS PROBLEM AND IT SERIOUSLY SUCKS!
    Tuesday, June 9, 2020 3:12 AM