SQLTeam.com | Weblogs | Forums

Assess synonym through linked server


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


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


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


AN IP address:

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

should work just fine


Books Online says you can't do this:

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


It's about half way down the page.


Thank you. Well noted.