Microsoft KB Archive/916278

= Error message when you upgrade Microsoft Dynamics CRM: &quot;Cannot DROP the index 'hind_%' because it is not a statistics collection&quot; =

Article ID: 916278

Article Last Modified on 1/4/2008

-

APPLIES TO


 * Microsoft Dynamics CRM 4.0
 * Microsoft Dynamics CRM 3.0

-



SYMPTOMS
When you upgrade from Microsoft CRM 1.2 to Microsoft Dynamics CRM 3.0, you receive the following error message:

Microsoft CRM 3.0 Server Setup failed.

Microsoft CRM 3.0 Server Setup did not complete successfully.

Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed.

Exception has been thrown by the target of an invocation.

Cannot DROP the index 'hind_%' because it is not a statistics collection.

When you upgrade from Microsoft Dynamics CRM 3.0 to Microsoft Dynamics CRM 4.0, you receive the following error message:

Microsoft CRM 4.0 Server Setup failed.

Microsoft CRM 4.0 Server Setup did not complete successfully.

Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed.

Exception has been thrown by the target of an invocation.

Cannot DROP the index 'hind_%' because it is not a statistics collection.



CAUSE
This problem occurs because the system cannot remove the hypothetical indexes that were created by the Index Tuning Wizard.

Notes
 * When you run the Tuning Wizard against the Microsoft CRM 1.2 databases, the Index Tuning Wizard creates the hypothetical indexes on the tables that are tuned. The system is supposed to remove the hypothetical indexes at the end of the tuning process. However, if the Index Tuning Wizard stops before it finishes the tuning process, the hypothetical indexes remain in the system.
 * The hypothetical indexes that were created by the Index Tuning Wizard have &quot;hind_%&quot; at the start of their names.



RESOLUTION
Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

To resolve this problem, remove any hypothetical indexes that were created by the Index Tuning Wizard. To do this, follow these steps:  Log on to the Microsoft SQL server as a user who has Microsoft SQL Server Administrator permissions. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer. In the Connect to SQL Server dialog box, click the Microsoft SQL server, and then click OK. On the SQL Query Analyzer toolbar, click the _MSCRM database in the Database list.  In the Query box, type the following command. DECLARE @strSQL nvarchar(1024) DECLARE @objid int DECLARE @indid tinyint DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name OPEN ITW_Stats FETCH NEXT FROM ITW_Stats INTO @objid, @indid WHILE (@@FETCH_STATUS <> -1) BEGIN SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']' FROM sysindexes i join sysobjects o on i.id = o.id WHERE i.id = @objid and i.indid = @indid AND (INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR (INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0))) EXEC(@strSQL) FETCH NEXT FROM ITW_Stats INTO @objid, @indid END CLOSE ITW_Stats DEALLOCATE ITW_Stats  On the Query menu, click Execute.</ol>

<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.

Keywords: kbmbscrm40 kbtshoot kbmbsupgrade kberrmsg kbmbsmigrate kbprb KB916278

-

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

© Microsoft Corporation. All rights reserved.