Home » SharepointRSS

unable to disable publishing and distribution

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
------------------------------

 

10 Answers Found

 

Answer 1

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]

 

Answer 2

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.

 

Answer 3

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!!

 

Answer 4

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


 

Answer 5

try to disable  replication using the sp_droppublication @publication='all',@ignore_distributor=1
 

Answer 6

Hi,

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

But nothing worked. Again same error... 

Any further suggestion ?
 

Answer 7

what error message do you get when you issue this command?

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

Answer 8

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

 

Answer 9

pherciaC,

run this: exec sp_dropdistributor @no_checks =1

then re setup your distribution  database and publishers.

 

 

 

Answer 10

pherciaC,

run this: exec sp_dropdistributor @no_checks =1

then re setup your distribution database and publishers.

 

 


I do not understand why, though I know the solution now, Your effort is appreciated! Could you explain it more clearly? 
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter