locked
CRM 2011: Search Errors After Installation of Update Rollup 10 RRS feed

  • Question

  • I installed Update Rollup 10 over the weekend.  Now when I search on the Account grid I get an "Invalid column name 'AccountId'" error.  When I search on the Contact grid I get an "Invalid column name 'ContactId'" error.  This error shows in the trace log and when I do a profile trace in SQL.  Any ideas?  No customizations have been changed.
    Tuesday, August 28, 2012 4:15 PM

Answers

  • I got a Generic SQL Error on Lead but Account/Contact were OK

    Quick Find has changed in UR10 and it appears from other posts/blogs that there are some problems with Quick Find with UR10.

    Quick solution is to use the new OrgDB Settings Tools and set EnableQuickFindOptimization to zero which will cause Quick Find to work in the pre- UR10 way.

    OrgDB Settings Tool is on the UR10 downloads page and information on how to use it is http://support.microsoft.com/kb/2691237


    Microsoft Dynamics CRM Specialist and MCT www.vigence.co.uk

    • Marked as answer by ryanbesko Tuesday, August 28, 2012 4:56 PM
    Tuesday, August 28, 2012 4:25 PM

All replies

  • I got a Generic SQL Error on Lead but Account/Contact were OK

    Quick Find has changed in UR10 and it appears from other posts/blogs that there are some problems with Quick Find with UR10.

    Quick solution is to use the new OrgDB Settings Tools and set EnableQuickFindOptimization to zero which will cause Quick Find to work in the pre- UR10 way.

    OrgDB Settings Tool is on the UR10 downloads page and information on how to use it is http://support.microsoft.com/kb/2691237


    Microsoft Dynamics CRM Specialist and MCT www.vigence.co.uk

    • Marked as answer by ryanbesko Tuesday, August 28, 2012 4:56 PM
    Tuesday, August 28, 2012 4:25 PM
  • Thank you for the quick response!  And it worked!
    Tuesday, August 28, 2012 4:56 PM
  • Thanks for the feedback. I've spent all day looking at Quick Find issues with UR10


    Microsoft Dynamics CRM Specialist and MCT www.vigence.co.uk

    Tuesday, August 28, 2012 4:59 PM
  • Julian or Ryan, would either of you be willing to share either the SQL that is failing (which could be captured with SQL tracing) or the fetch XML on the entities that you're seeing this problem on?  We'd like to better understand what's going on in your environments, as we never saw these types of issues in our testing of our quick find performance improvements for UR10.

    Thanks.

    Elliot

    Wednesday, August 29, 2012 4:36 PM
  • Julian or Ryan, one other quick question:  what is the collation of your database?

    Thanks.

    Elliot

    Wednesday, August 29, 2012 7:31 PM
  • Elliot

    I am off-site at the moment on a training course but may be able to access remotely and if so will get the information for you

    Julian


    Microsoft Dynamics CRM Specialist and MCT www.vigence.co.uk

    Wednesday, August 29, 2012 9:20 PM
  • Hi Julian,

    Just wondering if you've had a chance to take another look at this.  We would prefer customers not have to turn off the optimizations we made to quick find, so any information you can provide that might help us determine whether there's a general issue here or something more specific about your environment would be helpful.

    Thanks.

    Elliot

    Tuesday, September 4, 2012 8:10 AM
  • I am experiencing this same issue with Contacts and Accounts. Our collation is Latin1_General_CI_AS.

    Unfortunately, I'm not able to get the "OrgDBOrgSettings Tool for Microsoft Dynamics CRM 2011" tool to work. I get an access is denied error after entering my password (correctly) twice. Is there any other way I can turn this feature off?

    Thursday, September 6, 2012 9:57 PM
  • Thanks for sending the collation along.  Is there any chance you can also send along the query that is failing?  I'd like to take a look at what is getting generated on your environment, since as of yet we have been unable to reproduce this issue in-house.

    Instead of turning off the entire quick find optimization, I'd like to see if disabling the quick find record limit will address the issue.  If you look in the System Settings dialog you should see a new setting at the bottom of the "General" tab called "Quick find record limits are enabled", which is set to "Yes" by default.  Try setting it to "No" and see if that causes this issue to go away.  If it does that can also serve as a clue as to where this issue might be coming from.  If it's possible for you to capture the failing SQL query please do this before changing this setting.

    Thanks.

    Elliot

    • Proposed as answer by S Cousins Thursday, September 6, 2012 10:38 PM
    • Unproposed as answer by S Cousins Friday, September 7, 2012 2:47 PM
    Thursday, September 6, 2012 10:05 PM
  • Thank you Elliot. That fixed the issue!

    Error message was "Invalid column name 'AccountId'. Or "Invalid column name 'ContactId'.

    Thursday, September 6, 2012 10:32 PM
  • Thanks for your help.  I was able to see the query that is failing and have determined the issue.  Here are a couple of suggested workarounds for this:

    1. You can disable the "Quick find record limits are enabled" setting.  This setting, though, can make a big impact when the quick find search fields include related object names, such as "Created By" or "Primary Account".  So if your quick find search field set include these types of fields, I would advise leaving this setting enabled.

    2. The primary issue is with searching on address fields, which exist primarily on entities such as Lead, Account, and Contact (also Competitor).  Specifically, if you look at the "Add Find Columns" dialog for the quick find view of an entity the columns that are searched have checks in their check boxes.  Of these checked columns, if the one that is closest to the bottom is an address attribute (e.g. Postal Code, Address Line 1, City, State, etc) then you will hit this problem.  This problem can be avoided by either deselecting the bottom column so that it is not searched anymore and so that the checked column closest to the bottom is not an address column.  Alternatively, you can select another non-address search column that is lower down in the "Add Find Columns" list.

    I strongly discourage disabling the quick find optimizations entirely, especially if you are an on-premise customer.  These optimizations, when combined with appropriate indexes, can make a dramatic difference in the performance of quick find.

    Please let me know if any of you run into more issues.

    Thanks.

    Elliot

    • Proposed as answer by S Cousins Friday, September 7, 2012 2:47 PM
    Friday, September 7, 2012 4:16 AM
  • Recreating the views as you suggested worked great. I reset the quick find optimization flag and then removed the address fields and saved the view(s), and when I added the address fields back in to the view(s) and saved again, the error was gone when I searched the next time.

    I appreciate your help getting this resolved so quickly. THANK YOU Elliot!

    Friday, September 7, 2012 2:52 PM