Microsoft KB Archive/894546

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

Article ID: 894546

Article Last Modified on 7/25/2006

-

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.



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:  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. 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. Click Connect. 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. 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: <ul> Parent:Child Business Units</li> Business Units</li> User</li> None</li></ul>

When the Filter-by-privileges optimization is applied, organization-wide access still returns all the records for the All Views option.</li> 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.</li></ul> </li> 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: <ul> A custom view that you created</li> The All views view</li> The Advanced Find views</li></ul>

You do not have to select the views that start with &quot;My.&quot; These views are already restricted to the records that are available to the particular Microsoft CRM user who is logged on to Microsoft CRM.</li> After you select all the views to which you want to apply the optimization, click Add.</li> 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.</li></ol>

Note The changes that the Microsoft CRM Business Unit Constraint Tool makes take effect immediately because the tool updates the FetchXML conditions in the _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: <ol> Run the Microsoft CRM Business Unit Constraint Tool.</li> 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.</li> Click Connect.</li> 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.</li> Click By Business unit.</li> <li>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.</li> <li>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.</li> <li>Remove the hotfix. <ul> <li>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.</li> <li>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

</li></ul> </li></ol>

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.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_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

Keywords: kbmbsmigrate kbpubtypekc kbqfe kbhotfixserver KB894546

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.