Home > Uncategorized > SQL Server Join Limit Exceeds in Filtered Views (The Maximum number of tables in a query (260) was exceeded)

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.

pic13.JPG

‘ 

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.

Advertisements
Categories: Uncategorized Tags: ,
  1. January 27, 2007 at 12:15 pm

    i strongly suggest you upgrade to sql server 2005 limit is 256 tables per select 🙂
    http://msdn2.microsoft.com/en-us/library/ms143432.aspx
    DotNetOlympian
    !!!

  2. January 28, 2007 at 4:25 am

    wel the default was 256 in sql server too. but after applying a fix it became 260 lolz.

    Please read this article for more details:
    http://support.microsoft.com/kb/818406

    Ayaz

  3. DDY
    April 5, 2007 at 11:55 pm

    I’ve got a call into support on this issue. The MSCRM tech says that SQL 2005 fixes this but hasn’t any references. At first he said that sql 2005 raises the limit from 256 to 1024 but BOL disagrees. Then he said it had to do with the number of tables in views, not the number in the select. And told me that I’d have have better luck talking to the SQL guys if I wasn’t convinced. Seemed reasonable. I’ll post back if I learn anything.

    Don

  4. May 3, 2007 at 8:51 am

    I’d run into the same problem and found the temp tables the right solution.
    The normal report did run smoothly in SQL 2005 but in SQL 200 it failed, so I had to find a solution.

    Here is a sample for the code:

    IF OBJECT_ID(‘tempdb..#accounts’) IS NOT NULL
    DROP TABLE #accounts
    IF OBJECT_ID(‘tempdb..#months’) IS NOT NULL
    DROP TABLE #months

    CREATE TABLE #accounts (
    [AccountID] UniqueIdentifier ROWGUIDCOL NOT NULL,
    [name] nvarchar(160) COLLATE Latin1_General_CI_AS
    )
    CREATE TABLE #months (
    [AccountID] UniqueIdentifier ROWGUIDCOL NOT NULL,
    [month] smallint,
    [monthcount] smallint
    )

    DECLARE @SQL AS nvarchar(4000)
    SET @SQL = ‘SELECT acc.accountid, acc.name FROM (‘ + @CRM_FilteredAccount + ‘) AS acc’

    INSERT INTO #accounts
    EXEC (@SQL)

    INSERT INTO #months
    EXEC (
    ‘SELECT regardingobjectid as AccountID, 1, COUNT(*) as monthcount
    FROM filteredappointment
    WHERE
    regardingobjectid IN (SELECT accountid FROM #accounts) AND
    qnh_scheduledvisit = 1 AND
    YEAR(scheduledstart) = ‘ + @Year + ‘ AND MONTH(scheduledstart) = 1
    GROUP BY (regardingobjectid)’
    )

    — repeat this for each month ^^

    SELECT acc.accountid,
    acc.name,
    ISNULL(mnt1.monthcount, 0) as month1,
    ISNULL(mnt2.monthcount, 0) as month2,
    Etc..etc..etc..
    FROM
    #accounts AS acc
    LEFT JOIN #months as mnt1 ON (mnt1.month = 1 AND acc.accountid = mnt1.accountid)
    LEFT JOIN #months as mnt2 ON (mnt2.month = 2 AND acc.accountid = mnt2.accountid)
    Etc..etc..etc..

    IF OBJECT_ID(‘tempdb..#accounts’) IS NOT NULL
    DROP TABLE #accounts
    IF OBJECT_ID(‘tempdb..#months’) IS NOT NULL
    DROP TABLE #months

  5. January 11, 2010 at 3:46 am

    Generally I do not post on blogs, but I would like to say that this post really forced me to do so, Excellent post!

    I’m Out! 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: