Having Clause wont run on Linked Server

I am trying to find all affiliates that have more commissions from this
week to the prior week. The problem is in the having part where
"aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
trying to use "aff.affiliateid" to refer to the parent table.

The error I get is "Invalid column name 'Col1010'.", but ONLY on the
sql01 box; it runs correctly on the box where the affiliate database
is, sql02.

If I get rid of the alias of "aff" and use the full path, I get the
error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
more than the maximum number of prefixes. The maximum is 3."

Is this just a SQL bug, or is there a fix?

declare @.date datetime ; set @.date = '2/3/05'

select affiliateid
from sql02.affiliates.dbo.affiliates_sum aff
where day >= @.date - 7 and day < @.date
group by affiliateid
having sum(lead_commissions) > (
select sum(lead_commissions + sales_commissions) as total
from sql02.affiliates.dbo.affiliates_sum aff2
where day >= @.date - 14 and day < @.date - 7
and aff2.affiliateid = aff.affiliateid
group by affiliateid
)<scottelkin@.gmail.com> wrote in message
news:1108711888.673744.271790@.c13g2000cwb.googlegr oups.com...
>I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?
>
> declare @.date datetime ; set @.date = '2/3/05'
> select affiliateid
> from sql02.affiliates.dbo.affiliates_sum aff
> where day >= @.date - 7 and day < @.date
> group by affiliateid
> having sum(lead_commissions) > (
> select sum(lead_commissions + sales_commissions) as total
> from sql02.affiliates.dbo.affiliates_sum aff2
> where day >= @.date - 14 and day < @.date - 7
> and aff2.affiliateid = aff.affiliateid
> group by affiliateid
> )

This KB article might be relevant:

http://support.microsoft.com/defaul...kb;en-us;825019

If that doesn't help, or if you need a workaround, you could try using
OPENQUERY() to pass the query through to sql02, although since you need to
include a parameter value, you would need dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Simon|||(scottelkin@.gmail.com) writes:
> I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?

Judging from the error message, this is a bug in either SQL Server
or SQLOLEDB.

I created a table from the query and then ran the query on my box
with a loopback linked server, and the query completed succesfully.
I am running the beta of SP4, so it could be because the issue have
been fixed. However, the problem may be related to a specific query
plan, so it is difficult to tell for sure.

Info about SP4 Beta is here: http://support.microsoft.com/kb/290211.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp