05-15-15, 08:20 #1Registered User
- Join Date
- Sep 2013
Unanswered: Assess synonym through linked server
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
05-15-15, 10:58 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Use OpenQuery() to allow you to access all of the syntax features (such as synonyms) on a remote database server.
Based on your example, I'd use:Code:
SELECT * FROM OpenQuery(STAR, 'SELECT TOP 1 * FROM LIFE.dbo.websvr_view_beneficiaries')
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.