Question on Linked server vs Local

Posted on 2007-07-24
Last Modified: 2012-05-05
I am trying to fix a problem I'm having trying to debug a stored proc on my sql server (I'm actually doing it on the server itself)

I get the dreaded
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

After poking around I saw a suggestion to run

select @@servername

I get null after runnning it.

After more poking, I found a suggetion to do the following

Use Master
Sp_DropServer 'WEB02'
Use Master
Sp_Addserver 'WEB02', 'local'

When I run this I receive the following:

There are still remote logins for the server 'WEB02'.

I know web02 is setup as a linked server.  

when I do a

select * from master..sysservers

The following is returned:

1      225      WEB02      SQL Server      SQLOLEDB      WEB02      NULL      NULL      2006-11-30 17:20:47.583      NULL      NULL      NULL      NULL      0      0      WEB02                               1      1      0      0      0      0      1      1      0      0      0      0      NULL

Could someone explain to me exactly what  Sp_Addserver 'WEB02', 'local'  does?

Question by:johnnyg123
    LVL 14

    Accepted Solution

    Sp_Addserver 'WEB02', 'local'  does?

    defines the name of a remote server 'WEB02' to the 'local' server


    Defines a remote server or the name of the local Microsoft® SQL Server". sp_addserver is provided for backward compatibility. Use sp_addlinkedserver.

    sp_addserver [ @server = ] 'server'
        [ , [ @local = ] 'local' ]
        [ , [ @duplicate_ok = ] 'duplicate_OK' ]

    [@server =] 'server'

    Is the name of the server. Server names must be unique and follow the rules for Microsoft Windows NT® computer names, although spaces are not allowed. server is sysname, with no default.

    With multiple instances of SQL Server, server may be servername\instancename.

    [@local =] 'LOCAL'

    Specifies whether the server that is being added is a local or remote server. @local is varchar(10), with a default of NULL. Specifying @local as LOCAL defines @server as the name of the local server and causes the @@SERVERNAME function to return server. (The Setup program sets this variable to the computer name during installation. It is recommended that the name not be changed. By default, the computer name is the way users connect to SQL Server without requiring additional configuration.) The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in each server.

    [@duplicate_ok =] 'duplicate_OK'

    Specifies whether or not a duplicate server name is allowed. @duplicate_OK is varchar(13), with a default of NULL. @duplicate_OK can only have the value duplicate_OK or NULL. If duplicate_OK is specified and the server name that is being added already exists, then no error is raised. @local must be specified if named parameters are not used.

    Return Code Values
    0 (success) or 1 (failure)

    To execute a stored procedure on a remote server (remote procedure calls) running an earlier version of SQL Server, add the remote server using sp_addserver. To execute a stored procedure (or any distributed query) on a remote server running SQL Server version 7.0, use sp_addlinkedserver to add the server.

    To set or clear server options, use sp_serveroption.

    sp_addserver cannot be used inside a user-defined transaction.


    Author Comment


    Thanks so  much for the reply.

    so, if I'm trying to run

    Use Master
    Sp_DropServer 'WEB02'

    on the server web02 itself,  I'm not sure why I get the

    There are still remote logins for the server 'WEB02'    message

    does this mean I can just leave the exisiting web02 entry in the sysservers table so I would in effect end up with 2 entries for web02?  One would be a linked server entry and the other a remote server entry?
    LVL 14

    Expert Comment



    to display the remote servers that are set up. Might then be useful if you list what you've got.


    Author Comment


    I'm assuming you just wanted to see the entry for web02...if that's not right just let me know

    name         network_name   status   ID   Collation_Name  Connect_TimeOut     Query_TimeOut

    web02      web02                             rpc,rpc out,data access,use remote collation      3         NULL      0      0
    LVL 14

    Expert Comment

    "does this mean I can just leave the exisiting web02 entry in the sysservers table so I would in effect end up with 2 entries for web02?  One would be a linked server entry and the other a remote server entry?"

    Looks like there's just one entry - I don't believe there is a difference between a linked and a remote server... but not sure on this

    Author Comment

    I guess that's my question....what's the difference.

    Well...I do appreciate all your input .....doesn't look like I'm going to get the ultimate answer so I'll close this issue and award you the points.

    Thanks again!
    LVL 14

    Expert Comment

    No probs

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now