8.4 versus 8.1 execution plan union

[blom0344]

This not strictly framework related, but can perhaps be solved through the model or settings..

A complex report involving a union runs fine with 8.1 on both SQL2000 and SQL2005. The same report on 8.4 is executed differently , the entire SQL is executed on the database which causes the report to crash on SQL2000 because of too many tables accessed (256)
(Each of the 2 parts of the union seperately runs with no problem)

I am looking for a way to enforce 8.4 to use the 8.1 strategy (server side joins) or to enforce the Cognos server to perform the union , hoping I can get the report to work in 8.4 against SQL2000.

I played around with various settings (query execution sequential, limited local processing), but 8.4 keeps throwing an incredible large SQL against the database. It looks like the entire statement is repeated 5-6 times (when using a DB trace)

I would be pleased to hear suggestions on possible remedies!!