In CRM 2011 you can sometimes get the following sort of error message out of the blue:
Table alias a_9707c8e6f503e311835f00155d027e01 is not unique amongst all top-level table and join aliases
This will happen when you try to switch to a System View.
In this post I’m going to offer you my best guess as to when/why this happens and show you how to fix it.
When does this issue occur?
It could be coincidence but the issue has happened to me when both the following conditions are true:
- You have a view that shows a column from a related entity. For example, you have a System View showing Opportunities and that view pulls the geographic Region from the Account entity.
- You use the “Save As” feature to create a copy of a System View that you then go on to customise. You might do this if you have to create a lot of nearly identical views and don’t want to start from scratch with each one.
The issue basically occurs because at some point whilst a save is taking place, CRM corrupts the FetchXml portion of the view definition. In particular the FetchXml will be created containing duplicate link-entity sections.
To use SQL as an analogy it would be as though the tool you are using to create the SQL created two identical JOINS by mistake and used the same alias in each. When you come to execute that – the systems calls you out on it.
You can see a slightly more extreme example of this where the FetchXml has been corrupted not once but twice:
How do I fix it?
There are two possible fixes to this. One that sometimes works but I have reservations about and the other way which is more time consuming but definitely works in all scenarios.
Option 1 – Just delete the related entity column from the view
I have two reservations about this so on balance I probably wouldn’t do it this way.
The first issue is that it won’t work in the scenario I showed above – where you have 3 errant “link-entity” sections. If you remove the problem field and republish it will remove one of the three problem clauses, which still leaves you with duplicates.
The second issue is that even if you remove the problem column and it removes one of the duplicate link-entity sections – I have a nasty suspicion that it will leave a pointless link-entity block in place which will probably store up issues for later. I haven’t checked this point however so you’re mileage may vary if you use this approach
Option 2 – Manually fix the XML
The “nuclear” option is just to export the primary entity via a solution and edit the relevant XML directly. This is a bit of a pain, but if you go in and surgically remove only the duplicate clauses and reimport, the issue should be fixed
Update: Just found this MS article that indicates the issue is more to do with modifying filters on views than my theory of using the Save As button. The article also indicates that the problem was solved in UR15 but this will not retrospectively fix pre-existing views that are corrupt