Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013

    Unanswered: 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

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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:
    SELECT *
       FROM OpenQuery(STAR, 'SELECT TOP 1 *
          FROM LIFE.dbo.websvr_view_beneficiaries')
    Also double check to be sure that the Linked Server Login has permission to access to the synonym.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts