October 20, 2004 at 11:17 am
I'm having some problems with a cross-server query. As the query itself is rather complex and dynamically built, I've tried to construct a smaller example that illustrate the issue:
1. This query works fine:
select *
from [myServer].[myDB].dbo.contact c
where c.contact = (select c2.contact
from [myServer].[myDB].dbo.contact_2 c2
where c2.contact = c.contact)
2. If I wrap a max() around the subselect result, the query fails:
select *
from [myServer].[myDB].dbo.contact c
where c.contact = (select max(c2.contact)
from [myServer].[myDB].dbo.contact_2 c2
where c2.contact = c.contact)
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'c' does not match with a table name or alias name used in the query.
Any ideas as to what causes the problem? Are there any restrictions for syntax allowed in cross-server queries?
October 20, 2004 at 2:49 pm
How about something like this instead:
select top 1 *
from [myServer].[myDB].dbo.contact c
inner join [myServer].[myDB].dbo.contact_2 c2
on c.contact = c2.contact
order by c2.contact desc
should give you the same result but without a sub query.
October 21, 2004 at 6:45 am
I tried olavho's queries within our system (modified for one of our tables) and the MAX version worked just fine as one would expect. We are runnnig SQL 2000 SP3 something ("SP3a" I think). I've seen that kind of error message before when trying to perform subselects in an UPDATE but I see no reason for the code you gave to fail. If anything I would expect the first one to fail due to more than one row being returned should such data exist.
October 21, 2004 at 10:49 am
Thanks for the input. I believe we also use SP3, but I'll check if we have the latest update available. Could very well be that there is some versioning or configuration issue that I need to look for, since the query works for you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply