Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Large query against filtered views? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi

    We are Customising a CRM install to contain many custom entitys due to our requirements. I am creating a query agains CRM's filtered views to retrieve some of the data we need.

    It currently joins about 7 of the views together. When I try to join any additional ones I get an error from SQL.

    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 (260) was exceeded.

    Having a look at the excecution plan, I see that the filtered views each access a varierty of security/privelages tables in CRM. Thus casing Access many tables, in this case too many...

    Is there a better way of doing this? Or do I have to resort to creating a query against the actual CRM tables and not the filtered views??

    Any help would be apperciated.

    Cheers

    Andrew
    Friday, September 7, 2007 3:33 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    There are 3 ways around this:

    1. Upgrade SQL to SQL 2005; it does have a join limit, but it is much higher than 260 joins
    2. Rewrite the query to join a few of the views and write this to a temporary table, then join this to the other views
    3. Query against the underlying tables or views (e.g. there is an account view). You lose the security filtering of filtered views, though with clever design you may be able to use a combination of filtered and base views. Not also that users will not have SQL permission on anything other than the filtered views

    The first 2 are supported, but involve some work, whereas the 3rd option is probably the easiest but is unsupported

     

    Friday, September 7, 2007 7:55 AM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    There are 3 ways around this:

    1. Upgrade SQL to SQL 2005; it does have a join limit, but it is much higher than 260 joins
    2. Rewrite the query to join a few of the views and write this to a temporary table, then join this to the other views
    3. Query against the underlying tables or views (e.g. there is an account view). You lose the security filtering of filtered views, though with clever design you may be able to use a combination of filtered and base views. Not also that users will not have SQL permission on anything other than the filtered views

    The first 2 are supported, but involve some work, whereas the 3rd option is probably the easiest but is unsupported

     

    Friday, September 7, 2007 7:55 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi

    Thanks for that, it confirmed my suspicions. I will try to use the views, but im some cases I may have to use the base tables.

    Thanks

    Andrew
    Monday, September 10, 2007 4:42 AM