首页 > 代码库 > Could not update the distribution database subscription table. The subscription status could not be changed.

Could not update the distribution database subscription table. The subscription status could not be changed.

2024-09-03 15:51:15   215人阅读

在一个测试服务器删除发布（Publication）时遇到下面错误，具体如下所示

技术分享

标题: Microsoft SQL Server Management Studio
 
------------------------------
 
Could not delete publication ‘RPL_GES_MIS_QCSDB‘.
 
------------------------------
 
其他信息:
 
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
 
------------------------------
 
‘xxxxx‘ is not defined as a Subscriber for ‘xxxx\xxxx‘.
 
Could not update the distribution database subscription table. The subscription status could not be changed.
 
Changed database context to ‘xxxx‘. (Microsoft SQL Server，错误: 20032)

 

 

其实这个环境是克隆过来，生产服务器是配置过发布订阅，复制克隆后，修改过服务器名称，另外在实际服务器上，并没有真的订阅这个Publication，当然这个测试环境的复制可能还被人折腾过。这个就是我当前案例的环境。

遇到这个错误时，可以使用下面脚本删除所有Subcrition后，

USE DataBaseName;
GO
 
EXEC sp_dropsubscription 
    @publication =N‘RPL_GES_MIS_QCSDB‘,  --根据具体情况填写Publication名称
    @article= N‘all‘,
    @subscriber=N‘all‘,
    @ignore_distributor=1;

 

然后手工删除分发服务器（当然也可以使用下面脚本删除，根据实际情况，修改对应的数据库名称）。

use DatabaseName;
 
GO
 
exec sp_droppublication @publication = N‘RPL_GES_MIS_QCSDB‘, @ignore_distributor = 1
 
exec sp_helpreplicationdboption @dbname = N‘DatabaseName‘, @reserved = 1
 
use [DatabaseName]
 
exec sp_helppublication 
 
use [DatabaseName]
 
exec sp_replicationdboption @dbname = N‘DatabaseName‘, @optname = N‘publish‘, @value = N‘false‘
 
 

Could not update the distribution database subscription table. The subscription status could not be changed.


联系
我们