SQL2008R2 unable to drop linked server

Question

SQL2008R2 unable to drop linked server

Mortimer452

SSC-Addicted

Points: 423
More actions
February 6, 2017 at 1:01 pm

#318411
OK, so I screwed up a script and created a push subscription with a mis-typed server name. SQL created the subscription and linked server, but the server name was wrong so it obviously failed. I removed the subscription, set it up with the correct name and all is good.
I'm trying to drop the linked server with the wrong name, but SQL just won't let me. I get this message when I run sp_dropserver:
```
Msg 20584, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 67
Cannot drop server 'badservername' because it is used as a Subscriber to remote Publisher 'publishername' in replication.
```
Tried disabling subscriber on the linked server using sp_serveroption 'badservername', 'sub', 'off'
I can't find any trace of the bad server in replication monitor, it's definitely not setup as a subscriber anymore, just can't figure out why my publisher seems to think it is. Any ideas?
February 7, 2017 at 10:42 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Mortimer452 - Monday, February 6, 2017 1:01 PM
OK, so I screwed up a script and created a push subscription with a mis-typed server name. SQL created the subscription and linked server, but the server name was wrong so it obviously failed. I removed the subscription, set it up with the correct name and all is good.
I'm trying to drop the linked server with the wrong name, but SQL just won't let me. I get this message when I run sp_dropserver:
Msg 20584, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 67
Cannot drop server 'badservername' because it is used as a Subscriber to remote Publisher 'publishername' in replication.
Tried disabling subscriber on the linked server using sp_serveroption 'badservername', 'sub', 'off'
I can't find any trace of the bad server in replication monitor, it's definitely not setup as a subscriber anymore, just can't figure out why my publisher seems to think it is. Any ideas?

Try querying sys.servers to see what replication settings the linked server has -
SELECT name, is_distributor, is_publisher, is_subscriber FROM sys.servers

Sue

Mortimer452 SSC-Addicted Points: 423 More actions · Answer 2

Sue_H - Monday, February 6, 2017 2:21 PM
Try querying sys.servers to see what replication settings the linked server has -
SELECT name, is_distributor, is_publisher, is_subscriber FROM sys.servers
Sue

is_distributor, is_publisher, is_subscriber are all set to 0 in sys.servers

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Mortimer452 - Monday, February 6, 2017 2:40 PM
is_distributor, is_publisher, is_subscriber are all set to 0 in sys.servers

The only other place I can think of to look around and check is some of the tables in the distribution database. Those might have something to give you a clue of what bits from the first replication are still around. Maybe check these tables:
MSsubscriptions
MSsubscriber_info
MSpublications
MSdistribution_history
MSdistribution_agents

I usually execute sp_removedbreplication everywhere to clean up bits left over but there is never any replication left any more when I do that. Not of any use to you since you have the correct one up and running.

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 4

And I thought of one other one to check -
In the Publisher database, check sysreplservers. It's a system table. Maybe you have the bad server name in there.

Sue