sql linked server join query

Question

I am having issues running any query on joining a local DB with a DB from a linked server.

My query:

SELECT 

        [LocalDatabase].[dbo].[Record].[Project_ID],
        [LinkedServer].[Reporting].[dbo].[Active].[Name]

        FROM [LocalDatabase].[dbo].[Record] inner join 
             [LinkedServer].[Reporting].[dbo].[Active] ON
             [LocalDatabase].[dbo].[Record].[Project_ID] = [LinkedServer].[Reporting].[dbo].[Active].[Delivery_Number]

The errors:

Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "LinkedServer.Reporting.dbo.Active.Delivery_Number" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "LinkedServer.Reporting.dbo.Active.Name" could not be bound.

I am guessing my syntax is incorrect but I am unable to fix it. Can someone please suggest a solution?

If there is a better solution for me to run a select query on 2 databases which are on different servers, please mention it.

Answer 1

Try writing this using table aliases:

SELECT r.[Project_ID], a.[Name]
FROM [LocalDatabase].[dbo].[Record] r inner join 
     [LinkedServer].[Reporting].[dbo].[Active] a
     ON r.[Project_ID] = a.[Delivery_Number];