A view takes more than five seconds to display records in Microsoft CRM, or you experience SQL Server blocking issues and time-out errors (894546)


The information in this article applies to:

  • Microsoft CRM 1.2

SYMPTOMS

When you access one of the All Views options in Microsoft CRM 1.2, such as the All Leads view, the Active Opportunities view, or the Active Accounts view, you may experience one of the following symptoms:
  • The view that you select takes more than five seconds to display records.
  • You experience Microsoft SQL Server blocking issues.
  • If you try to update a record or save a record, you experience time-out errors. This symptom occurs if many thousands or millions of records exist in the table or tables that correspond to the view that you selected.

CAUSE

This problem occurs because of the way in which Microsoft CRM 1.2 returns records for a particular view. When you access one of the All Views options in Microsoft CRM 1.2, Microsoft CRM 1.2 generates an SQL statement. The SQL statement always returns all the records for the particular entity.

The Microsoft CRM SQL statement returns more than the required number of records when the following conditions are true:
  • You are logged on to Microsoft CRM.
  • You have a Microsoft CRM security role that limits you to viewing only the records from your particular business unit.
Therefore, the SQL statement generates a load that is larger than required on the computer that is running Microsoft SQL Server 2000.

RESOLUTION

Hotfix information

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem.

To resolve this problem immediately, contact Microsoft Business Solutions Technical Support to obtain the hotfix. You can log on to the Microsoft Business Solutions Support site and create a new support request. To do this, visit the following Microsoft Web site:

https://mbs.microsoft.com/support/newstart.aspx

You can also contact Microsoft Business Solutions Technical Support by telephone at (888) 477-7877.

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Business Solutions Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to any additional support questions and issues that do not qualify for the specific update in question.

This hotfix lets you restrict views so that less data is retrieved. Only the data to which the user has access is retrieved and then parsed by Microsoft CRM.

Installation information

Install this update on computers that are running Microsoft CRM 1.2 Server.

You must use the Microsoft CRM Business Unit Constraint Tool (MSCRMBUConstraint.exe) to apply the optimizations to the various views for this hotfix to take effect. To do this, follow these steps:
  1. Copy the MSCRMBUConstraint.exe file to the Bin folder on the Microsoft CRM 1.2 server, and then run the Microsoft CRM Business Unit Constraint Tool.

    Note By default, the Bin folder is located in the C:\Program Files\Microsoft CRM Server directory.
  2. Type the host name of the computer that is running Microsoft CRM 1.2. Or, type localhost, and then specify the port number to which you want to connect.

    Note You do not have to specify a port number if you use the default port. The default port is HTTP port 80.
  3. Click Connect.
  4. If you want shared records to be available for viewing by users, click to select the Select this check box to include shared entities check box.
  5. Click Suggest type of optimization. You receive the following message:This might take some minutes, but no data will be written during checking. Are you sure you want to see what type of optimization is better for you? Click Yes. After the optimization process is finished, the optimization setting changes to the optimization that the Microsoft CRM Business Unit Constraint Tool determines will work best. This determination is based on the number of shared records. The Filter-by-privileges option or the By Business unit option appears.
    • The Filter-by-privileges optimization filters the views by returning only records from the Microsoft CRM database that are based on the Microsoft CRM security role that is assigned to the Microsoft CRM users. These security roles include the following privileges:
      • Parent:Child Business Units
      • Business Units
      • User
      • None
      When the Filter-by-privileges optimization is applied, organization-wide access still returns all the records for the All Views option.
    • The By Business unit optimization filters the views by using the following logic. If a Microsoft CRM user is assigned a role that has Parent:Child Business Units privileges or Business Unit privileges, this optimization returns records for the business unit or for the business unit together with all the child business units.
  6. The middle panes in the Microsoft CRM Business Unit Constraint Tool display all the Microsoft CRM views. The views are grouped by entity. Select the Microsoft CRM views to which you want to apply the optimizations. We recommend that you select one of the following views:
    • A custom view that you created
    • The All views view
    • The Advanced Find views
    You do not have to select the views that start with "My." These views are already restricted to the records that are available to the particular Microsoft CRM user who is logged on to Microsoft CRM.
  7. After you select all the views to which you want to apply the optimization, click Add.
  8. The Microsoft CRM views from which you have to remove optimizations are listed in the lower pane of the Microsoft CRM Business Unit Constraint Tool. Select these views, and then click Remove.
Note The changes that the Microsoft CRM Business Unit Constraint Tool makes take effect immediately because the tool updates the FetchXML conditions in the Organization_Name_MSCRM database.

Prerequisites

This hotfix requires Microsoft CRM 1.2.

Removal information

You can remove this hotfix. However, you must remove the optimizations by using the Microsoft CRM Business Unit Constraint Tool before you remove this hotfix in Add or Remove programs.

If you do not remove the optimizations before you remove the hotfix, you will receive error messages in Microsoft CRM. You must then reinstall the hotfix, remove the optimizations, and then remove the hotfix again. End users will receive the following error message in Microsoft CRM when the users try to view a Microsoft CRM view that uses the optimizations:
Unexpected Error
An error has occurred. For more information, contact your system administrator.
If the DevErrors attribute is set to On in the Web.config file, you may also receive the following error message:
Error Description: An unexpected Error occurred.
Error Details: An invalid number of arguments was passed in to a condition.
Error Number: 0x8004216
Source File: D:\CRM\Core\src\platform\include\OMCommon\qb\querybuilder.inl
Line Number: 1415
Query String: /_grid/grid_data.aspxsortCol=name&sortDir=ascend&viewId]{00000000-0000-00AA-0000100001001}&filter=&pageNum=1
To remove the optimizations, follow these steps:
  1. Run the Microsoft CRM Business Unit Constraint Tool.
  2. Type the host name of the computer that is running Microsoft CRM 1.2. Or, type localhost, and then specify the port number to which you want to connect.

    Note You do not have to specify a port number if you use the default port. The default port is HTTP port 80.
  3. Click Connect.
  4. By default, any views that have the Filter-by-privileges optimization are displayed in the lower pane of the Microsoft CRM Business Unit Constraint Tool.
  5. Click By Business unit.
  6. If any views are listed in the lower pane of the Microsoft CRM Business Unit Constraint Tool, select the views, and then click Remove to remove the optimization.
  7. Click Filter-by-privileges to make sure that no views have this optimization. If no views have this optimization, quit the Microsoft CRM Business Unit Constraint Tool.
  8. Remove the hotfix.
    • To remove this hotfix from a computer that is running Microsoft Windows 2000, Microsoft Windows XP, or Microsoft Windows Server 2003, use Add or Remove Programs in Control Panel.
    • System administrators can use the Spunist.exe utility to remove this hotfix from a computer that is running Windows 2000, Windows XP, or Windows Server 2003. The Spuninst.exe utility is located in the %windir%\$NTUninstallKB894546$\Spuninst directory. For more information about how to use a package installer to remove a hotfix, click the following article number to view the article in the Microsoft Knowledge Base:

      832475 Description of the new features in the package installer for Windows software updates

Note To remove this hotfix on computers that are running Windows XP Service Pack 2 (SP2), you may have to turn on the Show updates feature. By default, the Show updates feature is turned off in Windows XP SP2. For more information about the Show updates feature, click the following article number to view the article in the Microsoft Knowledge Base:

884786 How to manage the Currently installed programs list in Windows XP Service Pack 2 and in Windows XP Tablet PC Edition 2005

Restart requirement

If you are prompted, restart the computer after you install the hotfix.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
File nameFile versionFile sizeDateTimePlatform
Adodb.dll7.10.3077.0117,46412-Apr-200515:57x86
Crmmsg.dll1.2.3297.12316,08817-Jan-200505:59x86
Crmquery.dll1.2.3297.199707,29622-Sep-200505:08x86
Crmqueryrecordset.dll1.2.3297.199232,17622-Sep-200505:07x86
Dlg_changeparent.aspxNot Applicable2,28902-Jun-200521:00Not Applicable
Edit.aspxNot Applicable41,82320-Sep-200521:08Not Applicable
Kb835295.dll1.2.3297.11110,97622-Sep-200505:02x86
Kb835296.dll1.2.3297.11110,97622-Sep-200505:02x86
Kb870578.dll1.2.3297.11510,97622-Sep-200505:02x86
Kb885754.dll1.2.3297.11310,97622-Sep-200505:02x86
Kb886283.dll1.2.3297.11410,97622-Sep-200505:02x86
Kb888175.dll1.2.3297.11210,97622-Sep-200505:02x86
Kb890485.dll1.2.3297.10910,97622-Sep-200505:02x86
Kb890598.dll1.2.3297.11410,97622-Sep-200503:02x86
Kb890600.dll1.2.3297.11010,97622-Sep-200505:02x86
Kb890690.dll1.2.3297.10710,97622-Sep-200505:02x86
Kb891715.dll1.2.3297.11310,97622-Sep-200505:02x86
Kb894114.dll1.2.3297.10910,97622-Sep-200505:02x86
Kb894546.dll1.2.3297.10110,97622-Sep-200505:02x86
Kb899469.dll1.2.3297.10410,97622-Sep-200505:02x86
Kb905861.dll1.2.3297.10310,97622-Sep-200505:02x86
Microsoft.crm.application.components.ui.dll1.2.3297.19992,96022-Sep-200505:04x86
Microsoft.crm.application.pages.grids.dll1.2.3297.199113,43222-Sep-200505:05x86
Mscrmbuconstrainttool.exe1.2.3297.19943,76822-Sep-200505:04x86
Mscrmcustom.dll1.2.3297.204121,56807-Sep-200519:52x86
Msvcr71.dll7.10.3052.4355,03222-Sep-200503:08x86
Pkgutil.exe1.2.3297.10188,79222-Sep-200505:07x86
Search.jsNot Applicable45,04620-Sep-200521:08Not Applicable
Crmhotfix.cdfNot Applicable7,70322-Sep-200505:14Not Applicable
Mscrmcustom.dll1.2.3297.204121,56807-Sep-200519:52x86
Updspapi.dll6.1.22.0371,93618-Nov-200418:45x86

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

For more information about the DevErrors parameter of the Web.config file, click the following article number to view the article in the Microsoft Knowledge Base:

857192 How to use the Web.config file to show more detailed error messages in Microsoft Business Solutions CRM (Microsoft CRM)

For more information about software updates, click the following article numbers to view the articles in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

887283 Microsoft Business Solutions CRM software hotfix and update package naming standards

Modification Type:MinorLast Reviewed:7/25/2006
Keywords:kbMBSMigrate kbQFE kbHotfixServer kbpubtypekc KB894546 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.