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.