SQL Server Join Limit Exceeds in Filtered Views (The Maximum number of tables in a query (260) was exceeded)
During developing reports, it’s so common to build query based on multiple views. Like when developing report for Account -> Opportunity -> Opportunity Products by using MSCRM Filtered Views, I have also faced the similar problems and the error comes with message “The Maximum number of tables in a query (260) was exceeded”.
The following screen shot shows the exact error message received in Enterprise Manager.
The likely cause for reaching the join limit is having a lot of pick lists or custom relationships on the entities you’re querying, as the filtered views have a join for each pick list or lookup field.
After a couple of days of research, here are my findings regarding how to solve these issues.
The supported way round this would be create a stored procedure split the select statement into more than one by creating a temporary table from the result of the joining 2 filtered views, then joining this to the rest. This can give a performance overhead if you’d have a lot of data in the temporary table.
- The other, unsupported route would be to not use filtered views for each entity. For example, if your query were retrieving accounts, opportunities and opportunityproducts, you could join filteredaccount to opportunity and opportunityproduct and rely on the security as implemented on filteredaccount (assuming in this case that a user would be able to see all opportunities for all accounts for which they have permission). Then you have to get round the fact that users will not have rights the underlying views (e.g. opportunity) or tables (e.g. opportunitybase). My preferred way round this is to create a new view in the CRM database that does the joins, and grant permission to the ReportingGroup to select from this view.