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


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

For example:

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]);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.