How do I JOIN to a Linked Server in a VIEW without using a CTE

  • I need to create a VIEW that joins a table in the current database (SSMS2019 environment), to a table in a database on a different server (SSMS 2014 environment). The VIEW will be created in the 2019 environment server.

    To start, I created a Linked Server in my 2019 server called [REMOTE_SERVER_2014_ENV] which successfully connects to the 2014 environment, and is given datareader + datawriter permissions to all the needed database on that server. However when I try to join on to it, I get an error that the multi-part identifier could not be bound.

    I can directly access the data in the table/datebase on the remove server by opening a new query window in my 2019 environment and using:

    SELECT * FROM  [REMOTE_SERVER_2014_ENV].[AddressSystem].[dbo].[tblAddress]

    However, when using "[REMOTE_SERVER_2014_ENV].[AddressSystem].[dbo].[tblAddress]" in a view, it fails saying the multi-part identifier cannot be bound.

    E.g.

    SELECT 
    [tblOrder].[fldOrderNumber],
    [REMOTE_SERVER_2014_ENV].[AddressSystem].[dbo].[tblAddress].[fldAddressLine1]
    FROM [tblOrder] INNER JOIN
    [REMOTE_SERVER_2014_ENV].[AddressSystem].[dbo].[tblAddress] ON
    [tblOrder].[fldAddressID] = [REMOTE_SERVER_2014_ENV].[AddressSystem].[dbo].[tblAddress].[fldAddressID]

    Is it possible to join on to other tables from other servers in this way within the view definition? I was also unable to drag and drop the remote table in to the query designer - the mouse pointer changed to a red circle with a line through it preventing me from doing so.

     

  • You can create a view using linked server tables, including with joins to local tables. You can't create a view including linked server tables with schemabinding.

  • what security context did you use when you created the linked server?

    2022-10-27 19_24_05-Window

    This is what my test uses. The account is dbo in my case.

    btw I prefer using synonyms for linked server objects (so consumers do not have to know the actual servername, but just "remote"_tablename, but that's probably just me.  ( the used prefix is "remote" so it is obvious for every consumer this may get uggly )

    create view wrk_Local_Remote as 
    SELECT *
    FROM [WRK].[dbo].[T20221012] T
    inner join [myremoteinstance].[AdventureWorks2017].[HumanResources].[Shift] RemoteTb
    on T.id = RemoteTb.ShiftID


    go
    Select *
    from wrk_Local_Remote;

    2022-10-27 19_22_58-Window

     

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • You are trying to use 4-part naming to reference the column.  That isn't allowed - you need to define an alias for the table and use that alias to reference the column.

    Better yet - define a synonym for the table and use the synonym.

    As for using the GUI to build a view, I would highly recommend that you don't do that.  There are a lot of issues with that GUI and many things that you can do in a view is not available in the GUI.  Much easier to just write the query and then add the create view than trying to drag/drop in the GUI.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply