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
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).
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!
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 ).
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).
).