Post reply

sp_dropserver

  • June 23, 2011 at 8:47 am

    #243258

    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.

  • June 23, 2011 at 8:56 am

    #1343093

    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

  • June 23, 2011 at 9:00 am

    #1343100

    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!

  • June 23, 2011 at 9:14 am

    #1343131

    Try this:

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

  • June 23, 2011 at 9:18 am

    #1343138

    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!

  • June 23, 2011 at 9:34 am

    #1343159

    Sorry, I'll ask some people if they have other ideas.

  • June 23, 2011 at 9:39 am

    #1343169

    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.

  • June 23, 2011 at 9:40 am

    #1343172

    are there quotes in the name of the server?

    Maybe this?

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

  • June 23, 2011 at 9:49 am

    #1343184

    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.

  • June 23, 2011 at 9:54 am

    #1343189

    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.

  • June 23, 2011 at 9:59 am

    #1343195

    Yeah, definately doesn't hurt being a DEV server. But I like to keep my servers clean of debris so to speak.

    I will definately keep you posted on this thread. I have a few MSDN cases going un-used so will most likely use one.

  • June 24, 2011 at 12:58 pm

    #1343874

    The solution can be found here:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/0dc28a77-0bf5-4beb-9e0e-711ed377dca8

    I found this by selecting the results of the NAME column from sys.servers into a variable and parsed out the two characters after the text ServerA; using the Substring and the ASCII function to find they were a carriage return and line feed.

  • June 24, 2011 at 2:55 pm

    #1343932

    Thanks for the update. That's good to know. Should have thought of looking for strange characters in there. My apologies.

  • June 24, 2011 at 3:05 pm

    #1343940

    As should I. Thanks for the input!

Viewing 14 posts - 1 through 13 (of 13 total)

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