Question on Linked server vs Local

Posted on 2007-07-24
Medium Priority
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
  • 4
  • 3
LVL 14

Accepted Solution

twoboats earned 2000 total points
ID: 19558476
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

ID: 19560121

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

ID: 19560329


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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 19565201

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

ID: 19566077
"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

ID: 19595654
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

ID: 19595689
No probs

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 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