SQLTeam.com | Weblogs | Forums

Assess synonym through linked server


#1

Hello All,
I am trying to write a stored procedure on my server (A) to query data from a remote server (B). I have created a linked server to the remote server. The remote server also has a linked server to another server (C). Synonyms are created on server B. My task is to query these synonyms. Is this possible? I wrote the following but it's throwing an error;

create procedure pr_test
as
select top 1 * from STAR.LIFE.dbo.websvr_view_beneficiaries

where STAR is the linked server on my server (A), LIFE is the database name on server B and websvr_view_beneficiaries is the synonym on server B

The error I get is "The OLE DB provider "SQLNCLI11" for linked server "STAR" does not contain the table ""LIFE"."dbo"."websvr_view_beneficiaries"". The table either does not exist or the current user does not have permissions on that table."

Any help will be appreciated.


#2

try SELECT TOP 1 * from [SERVERB].Life.dbo.websvr_view_beneficiaries


#3

Thanks.
I connect to server B using it's IP address.


#4

AN IP address:

SELECT TOP 1 * from [111.111.111.111].Life.dbo.websvr_view_beneficiaries

should work just fine


#5

Books Online says you can't do this:

You cannot reference a synonym that is located on a linked server.

https://msdn.microsoft.com/en-us/library/ms187552.aspx?f=255&MSPPError=-2147217396

It's about half way down the page.


#6

Thank you. Well noted.