SQL Server could not disable publishing and distribution - Invalid object name 'dbo.MSreplservers'.

Question

I have inherited a SQL 2016 server and am trying to set up replication. I looking at the server it appears that the distributor has already been set up, so I attempted to publish a database (transactional publication). I was getting "random" errors, so I thought I would disable distribution and start over.

First I verified that there were no publications active by running "select * from sys.databases where is_published = 1 or is_subscribed = 1 or is_merge_published = 1 or is_distributor = 1". The only database listed is the distribution database.

Second I right-clicked on the SSMS Replication node and selected "Disable Publishing and Distibution". At the end of the wizard I got the message "SQL Server could not disable publishing and distribution on '<ServerName>'." with the additional message "Invalid object name 'dbo.MSreplservers'."

I looked in the distribution database and the MSreplservers table is missing.

I tried running "exec sp_dropdistributor @no_checks = 1" and "exec sp_dropdistributor @no_checks = 1, @ignoreee _distributor = 1" and got the same message "Invalid object name 'dbo.MSreplservers'.".

I don't have a backup of the distribution database old enough that has the MSreplservers table in it.

Any advice on how to dig myself out of this hole?

Eric.

Answer 1

Hi Eric,

According to your query result, the Distributor is indeed configured on this SQL Server instance. What error did you encounter when configuring Publisher next? What is the "random" error? You can refer to this article to configure transactional replication.
https://www.sqlshack.com/sql-replication-basic-setup-and-configuration/

You encounter an error when using the Disable Publishing and Distribution Wizard. It may be that there are publishing and distribution objects that have not been completely removed. In my test, if I only configure the distributor on a SQL Server 2019 instance, and then use Disable Publishing and Distribution Wizard, I can simply successfully delete this configuration.

You can try to execute following t-sql statement:

use master   
go  
EXEC sp_dropdistributor @no_checks= 1;  
GO  

Please refer to this MS article and try to use T_SQL statement to disable the publishing and distribution.

===============================================

If the response helped, do "Accept Answer" and upvote it.

Answer 2

Did you try to create that table:

CREATE TABLE [dbo].[MSreplservers](
    [srvid] [smallint] NOT NULL,
    [srvname] [sysname] NOT NULL
) ON [PRIMARY]
GO

Answer 3

Well, shoot.

Msg 15329, Level 16, State 1, Line 1
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

If I query sys.databases where is_encrypted = 1, I get nothing returned. Would I be safe in using the force option?

Eric.

Answer 4

Prueba esto:

DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'DBNAME'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB