CRM 2011 - REPORT WIZARD EXCEPTION - "Table alias [xx] is not unique amongst table and join aliases in this inner query" RRS feed

  • Question

  • In a CRM 2011 Organization recently upgraded from CRM 4.0, an error is occurring while running some Reports built using CRM Report Wizard.  We have been able to consistently reproduce the error using Report Wizard to create new reports.  The reports with errors are not upgraded reports, they are newly created in CRM 2011.

    In the CRM application, the following  error is shown:

    Reporting Error

    The report cannot be displayed. (rsProcessingAborted)

    Performing a trace gleaned the following information:

    Crm Exception:
    Message: Table alias a_SOMEGUID is not unique amongst table and join aliases in this inner query
    , ErrorCode: -2147217104
    , InnerException: Microsoft.Crm.CrmException: Table alias a_SOMEGUID is not unique amongst table and join aliases in this inner query
       at Microsoft.Crm.Query.CrmInnerSelectCommandStrategy.ValidateTableAliasIsUniqueAndAddToTableAliasCollection(String tableAlias)
       at Microsoft.Crm.Query.CrmSqlSelectCommandStrategy.AddFromClause(LinkEntityExpression linkEntity)
       at Microsoft.Crm.Query.SelectVisitor.Visit(LinkEntityExpression linkEntity)
       at Microsoft.Crm.Query.SelectVisitor.Visit(EntityExpression entity)
       at Microsoft.Crm.Query.SelectPlan.GetSql()
       at Microsoft.Crm.Query.QueryPlan.get_Command()
       at Microsoft.Crm.Query.SelectFromSubQueriesPlan.GetSql()
       at Microsoft.Crm.Query.QueryPlan.get_Command()
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.RetrieveEntitiesForAggregateQuery(EntityExpression outerQuery, Dictionary`2 subQueries, ExecutionContext context)
       at Microsoft.Crm.ObjectModel.AggregationQueryService.RetrieveEntitiesForAggregateQuery(EntityExpression outerQuery, Dictionary`2 subQueries, ExecutionContext context)

    The Report includes 2 entities Primary Entity A (Parent) and Related Entity B (Child) with 1:N relationship.  The views for Entity A include attributes from Contact entity, thus Entity A's fetch queries contain a Link Entity:

    <link-entity alias="a_SOMEGUID" name="contact" from="contactid" to="new_somecontactid" link-type="outer" visible="false">
              <attribute name="middlename" />
              <attribute name="lastname" />
              <attribute name="firstname" />

    When the report is executed without Filter, it will complete successfully. However, adding any Filter to Entity A will result in the error. it looks like both the Filter (parameter) and Report Query contain the same Aliased entity value.

    We have verified the SavedQuery definitions, and none of them actually contain a duplicate value. 

    RU15 includes a fix for:

    "Getting error "Table alias 'alias name' is not unique" when opening certain views."

    Is this possibly resulting from the same issue?

    Has anyone else encountered this?  Might there be a way to resolve this?  Do you think this is an issue needing to be raised with Microsoft? 

    Thank you.

    • Edited by axium Friday, March 21, 2014 10:14 PM
    Thursday, March 20, 2014 7:58 PM