October 16, 2013 at 4:44 pm
we have restored an older database and with it came a subscription to a publication that we cannot seem to delete.
If we attempt to delete the subscription through SSMS an error pops up:
"'subscriber name' is not defined as a subscriber for 'server name'
could not update the distribution database subscription table. the subscription status cound not be changed."
there is not record for this subscription in the mssubscriptions table.
have tried various versions of sp_dropsubscription to no avail. we even get a completion message of "the subscriber was dropped" but, it does not go away.
Recreating the subscription from the subscriber side comes back with a message that the subscription already exists.
Trying to avoid deleting the publication as there will be recovery work to do on the application that uses the publication.
any help would be appreciated.
October 28, 2013 at 10:56 am
I believe that your problem to this link can help you.
Hugs.
January 8, 2014 at 6:47 pm
Depending on the subscription type, it is likely you will need to execute a stored proc on the subscriber to clean up the metadata there. Eg for merge:
NOTE: These are to be executed on the subscriber database:
exec sp_mergesubscription_cleanup @publisher = N'publisher sql instance name', @publisher_db = N'publication db', @publication = N'publication name'
And then a general cleanup one:
exec dbo.sp_MSdrop_mergesystables
January 10, 2014 at 12:16 pm
Thanks all for the suggestions. What worked in the end was comparing the mssubscriptions table with a known good configuration and then manually adding in a record for the orphaned subscription.
Once there, the subscription could be successfully deleted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply