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

0

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.

Best 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]);

Less good solution: if both tables don't have columns in the where clause with the same name.

SELECT top 100 *
FROM [ServerA].[MyDB].[dbo].[Table1] 
WHERE Exists( SELECT * FROM [ServerB].[TheirDB].[dbo].[TableA1] 
                  WHERE [foo] = [bar]);

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.