You are here

Table alias is not unique amongst all top-level table and join aliases

If you are seeing the above error, it might be due to incorrect FetchXML in the definition of a saved query such as a Quick Find view. I've encountered this on CRM 2011 with UR13 installed after modifying the filter criteria for a Quick Find view. The view includes fields from related entities.

When searching for records using Quick Find, CRM returns an error that includes the above title. To investigate, I added the entity in question to a solution, exported the solution and examined the FetchXML for the Quick Find view. Sure enough, there were multiple <link entity> entries to the same related entity.

I tried removing the view fields from related entities and publishing but this did not fix the error.

So to fix this I figured there are two options:

  • Fix the FetchXML in the solution file and then re-import
  • Directly update the FetchXML in the database (yes, this is unsupported and only works for on-premise)

I went for the second option. It was easy enough to identify the relevant row and table using the following query in SQL Server Management Studio (assuming the problem is with Accounts):

select * from SavedQueryBase where Name='Quick Find Active Accounts'

From the results, I copied the contents of the FetchXml column to Visual Studio, edited the query to remove duplicate <link-entity> references and then updated the SavedQueryBase table using: 

update SavedQueryBase

set

FetchXml='Insert corrected FetchXML here'

where Name='Quick Find Active Accounts'

Then I published customizations and Quick Find worked again. Of course, this does not explain why the problem occured in the first instance and it will probably occur again if the filter criteria is modified.

WARNING: Please do not try this unless you are very comfortable with using SQL. You should probably fix the issue by editing the solution file - but I have not tested this.

CRM Topics: 
CRM Versions: