Monday, April 20, 2015

How to remove Orphaned subscription

How to remove Orphaned subscription

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'. The subscription record does not exist at Subscriber.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
'S608161RGSW14' is not defined as a Subscriber for 'AJGECOMVDBP235\GTI'.
Could not update the distribution database subscription table. The subscription status could not be changed.
Changed database context to 'Transactor'. (Microsoft SQL Server, Error: 20032)

Using below script you can drop the sucbscription  you need tun below script in publication server 

In below script 
MYTran is the publication database 
MYTranGTI_MY_PUB is publication name 
MYTranMI is the subscriber database name 

USE  MYTran
GO
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
--enter your publication name
SET @publication = N'MYTranGTI_MY_PUB';
--enter subscriber name
SET @subscriber = N'MYTranMI';
USE MYTran
EXEC sp_dropsubscription
@publication = @publication,
@article = N'all',
@subscriber = @subscriber
,@ignore_distributor=1;
GO


Using below script you can drop the publication you need tun below script in publication server 

MYTran is the publication database 

MYTranGTI_MY_PUB is publication name 

USE MYTran
Go
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
--set your publication database here
SET @publicationDB = N'MYTran';
SET @publication = N'MYTranGTI_MY_PUB';
EXEC sp_droppublication
@publication = @publication
,@ignore_distributor=1;
-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption
@dbname = @publicationDB,
@optname = N'publish',
@value = N'false';