SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_dropserver


sp_dropserver

Author
Message
dashy
dashy
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 73
I was reviewing a development server today and it looks as if a developer created a linked server incorrectly.

Any ideas how to remove a linked server named:
'ServerA; UID=DEVAccount;PWD=DEVPwd'
Obviously the T-SQL they used to create the server had flaws.

Executing the following:
declare @Server varchar(100)
set @Server = 'ServerA; UID=DEVAccount;PWD=DEVPwd'
exec sp_dropserver @server = @Server

Produces the error:
The server 'ServerA; UID=DEVAccount;PWD=DEVPwd' does not exist. Use sp_helpserver to show available servers.

Sp_helpserver and sys.servers both show the exact text above as a valid linked server.

I reviewed the code for sp_dropserver and do not see any way I could remove it manually.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150710 Visits: 19455
AFAIK, the code is

sp_dropserver 'Servera'




No need for the name and pwd. You just include the server name as a drop or an add.

http://msdn.microsoft.com/en-us/library/ms174310.aspx

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dashy
dashy
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 73
That's the issue. The name of the server includes the user name and pwd.

It is not a valid server and it cannot connect to anything since it is not a valid server name. But I can't seem to find a way to remove it.

Thanks for the reply!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150710 Visits: 19455
Try this:

sp_dropserver '[ServerA; UID=DEVAccount;PWD=DEVPwd]'



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dashy
dashy
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 73
Yes, I tried using QUOTENAME already which is equivalent and it produced the same error. I did just try it again to confirm using both QUOTENAME and hardcoded like in your example.

Thank you again!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150710 Visits: 19455
Sorry, I'll ask some people if they have other ideas.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dashy
dashy
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 73
Thank you for your input. At least now I know it wasn't something simple I overlooked. I suspect there isn't a way to remove it. I may open a case with MS just to find out. If I do, I'll post my findings here.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150710 Visits: 19455
are there quotes in the name of the server?

Maybe this?

sp_dropserver '''ServerA; UID=DEVAccount;PWD=DEVPwd'''



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dashy
dashy
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 73
Produces the same error:
Msg 15015, Level 16, State 1, Procedure sp_dropserver, Line 42
The server ''ServerA; UID=DEVAccount;PWD=DEVPwd'' does not exist. Use sp_helpserver to show available servers.

There are no quotes. I suspect the semi-colons are throwing it off.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150710 Visits: 19455
Probably. I'm not sure it hurts to be there, but I can understand it being annoying.

If you open a case, would like to know how you remove it.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search