dropping a subscription in the subscriber first, how to fix it? I was looking for dropping a subscription, however, I made the mistake of dropping it in the subscribe, without dropping it at the publisher first, ignoring the warning from the picture below.




  TITLE: Microsoft SQL Server Management Studio
  
  This action will remove the selected subscription information, but not
  the previously replicated data, from database 'MY_DATABASE_Sub'. 
  Removing the subscription information should be done only if the
  subscription has been deleted at the Publisher, MY_PUBLISHER, or if the
  subscription is otherwise defunct. Removing an active subscription
  will result in errors at the Publisher.
  
  Are you sure you want to remove the subscription information?
  
  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;ProdVer=14.0.17119.0&amp;EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&amp;EvtID=CleanUpPushSubscriberConfirmation&amp;LinkId=20476
  
  ------------------------------ BUTTONS:
  
  &amp;Yes &amp;No


that is causing me the error below (in yellow), basically the subscription is showing up in the replication monitor, but it should not be there at all.



Question is:
How can I fix this error? Basically get rid of the ghost subscription showing up in the replication monitor and causing it become red flagged?
Always drop a subscription in the publisher first to avoid this error.

But if you happen to drop the subscription in the subscriber and get the error in the question above,
the way I sorted it out was to go to the publisher server and publisher database and run the script below, that generates a drop subscription command for each article still pointing to the wrong subscription.

this is the script to generate the script to run:

-- check the server and database
-- these should be the publisher server and publisher database

SELECT @@SERVERNAME
-- CTSTGDB1

SELECT DB_NAME()
-- APIASTG_Repl_Pub

-- go to the publication database
USE APIASTG_Repl_Pub
GO

--check how things are (works for before and after)
sp_helppublication


sp_helpsubscription



--script to generate the drop of all articles in the BANDIT subscription
USE APIASTG_Repl_Pub
GO

select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
, s.dest_db
,SQL_='exec sp_dropsubscription ' + CHAR(13) + 
      '     @publication=' + '''' + db_name() + '''' +  CHAR(13) + 
      '    ,@article='  + '''' + SA.NAME + '''' +  CHAR(13) +             
      '    ,@subscriber=' + '''' + UPPER(srv.srvname) + '''' +  CHAR(13) +       
      '    ,@destination_db=' + '''' + s.dest_db + '''' 
            +  CHAR(13) + CHAR(10) 

from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
where s.dest_db = 'MY_WRONG_SUBSCRIPTION'

-- get the _SQL generated above and open a new query, 
-- on the same server, same database, run it
-- problem should be solved!!!


Below is an example - a partial list of the script generated after running the script above.

exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Airport'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_ArrivalCalendar'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation_Details'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  


The script generated should be run in the publisher (server and database) 

this is the picture from the replication monitor:
note on the right hand side before there were 2 lines, one with a red error cross on it, that was the subscription we wanted to delete and now it has been deleted:


there are still errors on this replication monitor, but they are unrelated to this subscriptions as they are on a different server.

I would like to include a link to the question below because it might be useful when you have problems deleting a publication 

SQL Agent still attempting replication for deleted publication