I am, as some of you may remember, creating an esimating application and I am now starting to see the light at the end of the tunnel, or so I thought.
There are several takeoff tables because each one represents a different aspect of the estimate. There is a product takeoff, transportation takeoff, sitwork takeoff, et.... approx 20 tables in all. Now I am trying to create the bid documents using crystal reports, and because of the nature of crystal reports, I have to create a view that comprises all the tables above in a generic form, Quantity | Price | Subtotal |Adjustment | Total.
I managed this by creating several smaller views of all the takeoff tables and then created a view of the union of these smaller views. I just went to union one of the final views (#13) and I got this error message :
[Microsoft][ODBC SQL Server Driver][Sql Server] Could not allocate ancillary table for view or function resolution.
The maximum number of tables in a query (256) was exceeded.
I am assuming that this is because all the views reference a set of tables and if two views reference the same table, this is counted as multiple tables?
What should I do? Please don't say rethink my desing?
Well, you should at least scrap the View strategy. Use a single stored procedure instead. It is much easier to debug and administer.
Hehehe, you were going to say it!!!!
I was thinking of that when I got up this morning. I was also thinking, I should create a ##Temp table and populate it in the stored proc and link the report to this table. Would this be a viable approach?
What happens to a ##Temp table if the client machine crashes for any reason?