Could not allocate ancillary table for view or function resolution.
-
I get this error when I try to run this query. Can anyone please advise a way around?
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (256) was exceeded.
I use the view as a datasource for a Crystal Report that I use...I have the view below that causes the error when I try to run it...
ALTER View v_JobstartPlanProductionSummary as
select distinct div.division,r.region,IsNull(a.CompleteJobStarts,0) CompleteJobStarts,
IsNull(b.CompleteMarkets,0) CompleteMarkets,
IsNull(c.PCCs,0) PCCs,
IsNull(i.ERCs,0) ERCs,
IsNull(d.ChangeRequests,0) ChangeRequests,
IsNull(e.Cancels,0) Cancels,
IsNull(f.Restarts,0) Restarts,
IsNull(j.CancelledRestarts,0) CancelledRestarts,
IsNull(g.AvgHours,0) AvgHours,
IsNull(h.AvgBackLog,0) AvgDaysInBank
from Divisions div join Regions r on div.division=r.division
left outer join v_RegionCompletes a
on r.region=a.region
left outer join v_RegionMarkets b on r.region=b.region
left outer join v_RegionPCCs c on r.region=c.region
left outer join v_RegionCRs d on r.region=d.region
left outer join v_RegionCancels e on r.region=e.region
left outer join v_SumRegionRestarts f on r.region=f.region
left outer join v_RegionAvgManHours g on r.region=g.region
left outer join v_RegionAvgBackLog h on r.region=h.region
left outer join v_RegionERCs i on r.region=i.region
left outer join v_RegionCancelledRestarts j on r.region=j.region
-
Not sure but I think it may be the way that Crystal is using the view... I believe that it creates a different instance of the view for each instance that it is called on the Crystal report page AND those same instances are recreated for each page of the report.
Rather than using a view, use the same code to create a stored procedure and call the SP from Crystal.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Actually the error is coming from SQL Server. I pulled them all together in Crystal and it worked
-
Your new view is running multiple joins against other views that could well be doing joins against multiple tables. Check the other views and see if you are exceeding the 256 table limit.
-
Take a look at this article. This problem is not crystal related its sql related. If you execute your query in query analyzer you will get same error message.
http://support.microsoft.com/default.aspx?scid=kb;en-us;828269
-
This is the classic views-of-views problem in SQL Server the best approach is to use as many base tables as posible and you will have effectively solve the problem. Try to stay away from that approach as much as you can because usually when you use this approach you may en up not only with this problem but also with views that are performing uneeded work!
HTH
* Noel -
Like I said, "Rather than using a view, ..."
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply