1

I am comparing two tables on different Servers and the query I am running looks like this: 

Select top 100 *
From [ServerA].[MyDB].[dbo].[Table1]
    Where Exists( Select * from [ServerB].[TheirDB].[dbo].[TableA1]
    Where [ServerA].[MyDB].[dbo].[Table1].[foo] = [ServerB].[TheirDB].[dbo].[TableA1].[bar])

This is not working and throwing error 4104 on [ServerB].[TheirDB].[dbo].[TableA1].[bar] even though bar is a valid column and 

Select * from [ServerB].[TheirDB].[dbo].[TableA1]

Works fine

Randi Vertongen
  • 15,913
  • 4
  • 31
  • 58
levi Clouser
  • 123
  • 6

1 Answers1

1

The 4104 error occurs because you are using 5 part naming in your where clause.

For example:

SELECT TOP 100 *
FROM [ServerA].[MyDB].[dbo].[Table1]
WHERE [ServerA].[MyDB].[dbo].[Table1].[foo]=5;

Will throw the error:

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "[ServerA].[MyDB].[dbo].[Table1].[foo]"  could not be bound.

Solution: using aliases

SELECT top 100 *
FROM [ServerA].[MyDB].[dbo].[Table1] AS T1
WHERE Exists( SELECT * FROM [ServerB].[TheirDB].[dbo].[TableA1] AS T2
              WHERE T1.[foo] = T2.[bar]);
Randi Vertongen
  • 15,913
  • 4
  • 31
  • 58
  • Thanks for the answer, is there somewhere you would recommend for me to read more about this? – levi Clouser Jan 09 '19 at 20:10
  • @leviClouser no problem, I could point you to the 4 part naming convention for example this one: https://www.enhansoft.com/blog/what-are-the-four-part-names-of-a-sql-server-object but I am not sure if that will help you as much. As far as why 5 part naming is not possible, i don't immediately have any good blogs or links to share. Linked servers are a tricky subject, and can have some hard to debug problems: https://www.simba.com/docs/ODBC_QuickstartGuide/content/quick_start/windows/troubleshooting/linkedserver.htm, risks: https://dba.stackexchange.com/questions/40184/linked-server-risks etc – Randi Vertongen Jan 09 '19 at 20:15