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

    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
    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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:
    Code:
    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.

    -PatP
    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
  •