You cannot create a Local sql Server as a Link server

Question

You cannot create a Local sql Server as a Link server

muntasiriub

SSCommitted

Points: 1810
More actions
April 17, 2005 at 8:02 am

#163362

Dear ALL,
                I have inherited a server which shows following massage when I use 4 part table name.
My query is:
select         sum(amount) AS Event
from MylocalServer.RA_P07.dbo.P07_C60A21_stage
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server ' MylocalServer ' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
When I use sp_addlinkedserver from Enterprise Manager it shows following Error:
    You cannot create a Local sql Server as a Link server
And on sysservers table I have found no entry for MylocalServer
  Can any one Help me on that
Thanks In Advance
Muntasir

..Better Than Before...

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Have you changed the name of the computer since installing SQL Server? I recall some sort of problem that you would have to drop the old computer name and add the new computer name somehow - but I don't know how... I do recall that it was more of a problem with SQL Server 7 than 2000 - but maybe this could be one of the problems?

John Mayo SSC Journeyman Points: 94 More actions · Answer 2

I usually use TSQL scripts to create linked servers and I know that you can create one the "loops back" to the local server. I tend to use the machine name explicitly. If you aren't then that might be the problem you are having.

wz700 SSCertifiable Points: 7133 More actions · Answer 3

Try

USE master

GO

EXEC sp_addlinkedserver

@server='anyname',

@srvproduct='',

@provider='SQLOLEDB',

@datasrc='(local)'

GO

EXEC sp_addlinkedsrvlogin 'anyname', 'false', 'sa', 'Admin', NULL

go

select * from anyname.master.dbo.sysobjects

Morten Baden Rohde SSC Veteran Points: 259 More actions · Answer 4

The master.dbo.sysservers holds the name of all SQL servers known to the local SQL Servers, including its own name. This srvname has srvid=0.

If you run the command:

select @@SERVERNAME

the result should be the name of the server, if this is not the case you can change this value by performing the following commands:

sp_dropserver @@SERVERNAME

GO

sp_addserver 'computername(\Instancename)', LOCAL

and restart the SQL Server.

The SQL Server inits the variable @@SERVERNAME when it starts so you have to restart the sql server every time you change the value of srvid=0 in master.dbo.sysservers.

You may run into problems if @@SERVERNAME is set wrong.

Regards Morten Baden Rohde

muntasiriub SSCommitted Points: 1810 More actions · Answer 5

THX !!!

genius Newbie !!!

The Problem is solved......

It Is Working now

100000** 4 u

--Muntasir

..Better Than Before...

muntasiriub SSCommitted Points: 1810 More actions · Answer 6

Old Hand Thx,

Though it does not solve my posted problem, but help me to think about add link server through TSQL insteed of Enterprose Manager 's GUI.

--Muntasir

..Better Than Before...