Corruption of Views and Table Alias Duplicate in CRM 2011 after Polaris update
After Polaris, I have four customer solutions where I can’t create new or copy existing system views. When I do, I get the following error message:<Message>System.Xml.XmlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #FF39F24C</Message> <Message>Table alias a_6fc316606a37e011850618a90573b8df is not unique amongst all top-level table and join aliases</Message>
Of course, the GUID can differ in your error case. In my case the problem was related to a Custom Entity.
In addition, you will notice strange looking related entity lines if you look at the Filter definition of the copied view:
I ran a Microsoft support case, but at that time they couldn’t find a solution, and told me that it will be fixed in RU14. But now half a year later it is still a problem to my four customers’ Online organizations.
Researching the XML view definition you will easily notice the error – the link-entity is created as a duplicate with the duplicated GUID:
Solution 1 – just modify the view definition itself
One solution, which I have relied on until now, was to create the new system view or copy an old one; then export the XML definition in a simple solution (containing only this single entity) and finally manually stripping away the duplicated “link-entity” section in the XML. This worked fine, but was hard work since I used a lot of system views which need to be recreated semiannually. Other people have also already found this a feasible work around, i.e.:
- Imagine Technology Group: http://thinketg.com/crm-2011-error-resolution-table-alias-a_dc9b80f8c78146d89fd6a3b610836975-is-not-unique-amongst-all-top-level-table-and-join-aliases/
- SalesMetrix: http://blog.salesmetrix.com/2013/02/08/corruption-of-views-dynamics-crm-2011-online-dec-2012-service-release/
- Feridun: http://fkbase.info/node/161
Help from other resources
Today I needed to copy the views semiannually and during that process I researched and found that now other people are facing similar errors. Thanks to this I think I now have a solution that can solve the error permanently! So thanks to these guys here for pointing me in the right direction:
- Thanks to Bob Hatcher for revealing that new system views (and copies) are related to the Advanced Find XML definition (http://social.msdn.microsoft.com/Forums/en-US/e17c9a53-6fe2-435f-a1ec-058899ffb1ef/issues-with-advanced-find-dynamic-lists-since-polarisr12 )
- Thanks to Mayank Agrawal for pointing to the Advanced Find view definition – I assume his solution is the similar the the one I show below http://social.microsoft.com/Forums/en-US/913413a5-de99-44d2-88a8-80c36e620275/issues-with-quick-find-and-crm-2011-rollup-12
Solution 2 – clean up Advanced View Definition
Here is a walk through of a solution that seems to have fixed the issue permanently for me. Overall its about cleaning up the Advanced Find XML definition since this seems to be the main problem. You need to repeat the process for each entity containing the problem.
- Open the entity’s System View definitions
- Open the “Advanced Find” view definition
- Clean up the Advanced Find view column to ONLY contain columns from the Entity itself (NO columns from related entities must be contained as view columns)
- Publish Entity
- Create simple “Temp” solution and add the Entity above (and only this Entity)
- Export Temp solution
- Backup the Zip file (just in case you need to go back)
- From the Zip file, extract “customizations.xml”
- Open customizations.xml in text editor
- Locate the “Advanced Find” view definition:
- Strip out the link-entity line(s), since there are no references to related entities anymore (they were cleaned out in step 3, but CRM does not clean up the fetchxml)
- Save the XML file
- Re-insert it into the Zip solution file (remember to keep the copied one as a backup)
- Re-import the solution into your Organization and publish the entity
Now you should be able to create new System Views or copy existing ones (even copy ones with View columns from related entities).
In my case I only had one link-entity line – and my problem was related to a Custom Entity I created. Hopefully this solution method is generic to other entities as well.
Comments are appreciated in case you find other solutions.
Update: also have a look at what Joe says here: http://www.powerobjects.com/blog/2013/11/20/corrupted-marketing-list-views-after-installing-ur-12/?utm_source=feedly
Excellent post- however I found in our on-premise implementation of Dynamics CRM 2011 (with roll up 13 deployed) that all it took was to remove the linked entity fields from the Advanced Find View display columns, publish and the dreaded “table alias is not unique” error is resolved! No need for the exporting of the solution, editing the xml and reimporting of the solution.
Thanks for your addition to my post – I’m glad to hear that the problem no longer are that complicated to get rid of this problem.
I was just about to do a similar post describing this exact problem when I found yours.
I didn’t realise that the Advanced Find XML was linked to the problem so thanks for sharing!
I had the same issue and came up with a similar solution: