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?
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;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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply