Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Wow, big problem?

    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?

    Mike Bujak

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rethink your desi---oh, never mind...

    Well, you should at least scrap the View strategy. Use a single stored procedure instead. It is much easier to debug and administer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by blindman
    Rethink your desi---oh, never mind...

    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?

    Mike B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •