Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_dropserver Expand / Collapse
Author
Message
Posted Thursday, June 23, 2011 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 12:27 PM
Points: 9, 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.
Post #1130495
Posted Thursday, June 23, 2011 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1130502
Posted Thursday, June 23, 2011 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 12:27 PM
Points: 9, 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!
Post #1130509
Posted Thursday, June 23, 2011 9:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1130540
Posted Thursday, June 23, 2011 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 12:27 PM
Points: 9, 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!
Post #1130547
Posted Thursday, June 23, 2011 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1130568
Posted Thursday, June 23, 2011 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 12:27 PM
Points: 9, 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.
Post #1130579
Posted Thursday, June 23, 2011 9:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1130582
Posted Thursday, June 23, 2011 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 12:27 PM
Points: 9, 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.
Post #1130596
Posted Thursday, June 23, 2011 9:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1130602
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse