October 1, 2007 at 2:25 pm
We have a development server where I was trying to setup a Linked Server. When I tried to set it up I was told that I couldn't do it because it already exists. When I started checking around, I found that there are a very large number of SQL Servers setup as Remote Servers on this server. I don't know how they got there and none of the other DBAs claims to know how they got there.
To make matters worse, when I try to delete the Remote Server, I get the error:
Error 20583: Cannot drop server 'SERVERNAME' because it is used as a Subscriber in replication.
The frustrating part is that I cannot find any reference to it in any of our replication settings.
Anybody got any ideas?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 1, 2007 at 3:04 pm
hawg (10/1/2007)
We have a development server where I was trying to setup a Linked Server. When I tried to set it up I was told that I couldn't do it because it already exists. When I started checking around, I found that there are a very large number of SQL Servers setup as Remote Servers on this server. I don't know how they got there and none of the other DBAs claims to know how they got there.To make matters worse, when I try to delete the Remote Server, I get the error:
Error 20583: Cannot drop server 'SERVERNAME' because it is used as a Subscriber in replication.
The frustrating part is that I cannot find any reference to it in any of our replication settings.
Anybody got any ideas?
This is a well known issue in the replication area. Remote servers are used when you add subscribers to the publication. When you are sure that you are not replicating anything else to those subscribers all you need to do is to "de-register" them from the distributor/publisher.
Steps are :
1. Use sp_helpserver to get information about the server.
2. Use sp_dropsubscriber to de-register it.
3. Use sp_dropserver to drop it.
4. At this point you can re-use that name.
Hope this helps,
* Noel
October 1, 2007 at 3:14 pm
Thank you. I'll do what you said. It's development, so even if I mess something up, we can always get back to where we were.
My knowledge of replication is still very limited (in practice). I understand it, I just haven't done much of it.
The thing that is so confusing about all of this is that we have never setup replication for this (or any of these servers). In fact, the only replication we have setup is internal to a single server, just database to database.
We cannot figure out how they even got there. We can't find them referenced anywhere else.
Thanks again.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 1, 2007 at 3:17 pm
good luck,
Let us know how it went 😉
* Noel
October 1, 2007 at 3:22 pm
I did what you said and everything worked like a charm!
I was able to add the server as a Linked Server and now I can query directly against the server.
At least now we can go forward. We've just got to figure out how these all got there in the first place.
Thanks again.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 1, 2007 at 3:25 pm
Great, glad it worked for you.
Unfourtunately can't help you on the "how it happened" 🙂 someone must have setup replication in it at some point but is up to you to find who :D:D
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply