SQLTeam.com | Weblogs | Forums

Could not find server in sys.servers


#1

I am receiving the following message

Msg 7202, Level 11, State 2, Line 4
Could not find server '10.194.2.4' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Here is my query:

DECLARE @Site VARCHAR(50)
SET @Site = 'ZSW'

select * from
(
select distinct v.VEND_ID, v.VEND_LONG_NAME as VEND_NAME,l.Plat_Vendorkey, a.LN_1_ADR + ' '+a.LN_2_ADR as [Address], a.City_Name,
a.Mail_State_DC, a.Postal_CD

from [10.194.2.4].CP_Migration.dbo.L_CP_Plat_Vendorkey l
left join [10.194.2.4].WEBAPP_CP.DELTEK.v_VEND v
on v.VEND_ID = l.CP_Vendorkey
inner join [10.194.2.4].WEBAPP_CP.DELTEK.v_VEND_ADDR a
on a.vend_id = v.vend_id

where Plat_Vendorkey like @Site+'%' --order by vend_ID
)iq

Your help is always greatly appreciated. Thanks!


#2

Not sure but a guess would be that the IP address is not accepted as it is really looking for the server name/instance.

So it would be [MyServerName\MyInstance].database.schema.object or [MyServerName].database.schema.object if a default instance.


#3

To add to what djj55 suggested, if you connect to the default instance of SQL Server on 10.194.2.4 and run the following query you will see a list of linked servers:

SELECT * FROM sys.servers

The server name you specify in the four-part naming has to be one of the names listed in the name column. When you add a linked server, you can name it to what makes sense to you (although naming it to an IP address might not be a great idea - it might cause more confusion than clarity).


#4

Just in case it helps clarify the situation

AFAIK you cannot query a four-part name - i.e. ServerName.DatabaseName.SchemaName,TableName unless you have previously defined/created the Linked Server. The fact that you may have a SQL Server on 10.194.2.4 is, by itself, not enough.

We have done that (used the IP address as the Linked Server Name), but I can't now remember why (rather than using a Name) and whether it turned out to be a good idea, or not! Clearly if the Server might change IP Address in the future it would not be a good idea!


#5

Thanks Kristen for stating what I was trying to say in clear and concise English. (But English is not my native language, I speak American, so I have a valid excuse :grinning: ).

In my previous post I said that the sys.servers on the target server (10.194.2.4) should have an entry for 10.194.2.4. That is incorrect. What I should have said is that the sys.servers on the server FROM which you are running the query should contain the entry for the linked server (10.194.2.4).


#6

Thanks guys! That helps.


#7

Remember to like the posts that you find helpfull :wink: