Error message Max tables exceeded 256

I'm getting this error message when I run a an SP. I'm query off views that join to multi tables but I know that they dont exeec 256. Here is the error "Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (256) was exceeded."

Can you a) post the query so we can see and/or
b) in QA, display estimated query plan.

I have a feeling, along with the tables in the underlying views, sql is probably creating working tables, to a magnatude enough to generate the error. If thats the case, then its an optimization issue, on the views, the sproc or both.

I assume you're not hoping of taking advantage of any existing indexes.
I think 'Inside SQL Server 2000' states that any query joining more than four tables will not be optimized, because of the possible number of permutation on how to join the tables.

Sorry for not getting back to you sonner, been very busy. Anyways, the SP is to big to for me to want to post it. The SP involved an insert into statement that involed many views that referenced many tables But in how I solved the issue was to run index tuning wizard on the on select statments from the (views). I indexed some of the tables everything seemed to work out. This reduced 20 min long Queries to 1 minute. I also ran a defrag because DBCC ShowContig and it reported under 40% scan dinsity on some of the more bigger tables one even reported 14%.

Just to make a comment on Frank's comment. A query with more than 4 tables will be optimized, but not fully. The statement will essentially be broken up into groups of tables and they are optimised. so a query with 12 tables may be broken into 3 sets by the optimiser where each set is optimised and then the join between the 3 sets is optimised. This is when a properly ordered set of tables can make a significant improvement.

On many of the look up tables I noticed that they contained CLUSTERED INDEXES. Provided that the statistics were out dated by far. Do you think the query optimizer was utilizing these CLUSTERED INDEXES instead of Table scans.