Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when you upgrade Microsoft Dynamics CRM: "Cannot DROP the index 'hind_%' because it is not a statistics collection"

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

View products that this article applies to.

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.

↑ Back to the top

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 "hind_%" at the start of their names.

↑ Back to the top

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:
  1. Log on to the Microsoft SQL server as a user who has Microsoft SQL Server Administrator permissions.
  2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  3. In the Connect to SQL Server dialog box, click the Microsoft SQL server, and then click OK.
  4. On the SQL Query Analyzer toolbar, click the Organization_Name_MSCRM database in the Database list.
  5. 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
  6. On the Query menu, click Execute.

↑ Back to the top

Status

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

↑ Back to the top

Keywords: KB916278, kbmbscrm40, kbmbsupgrade, kbmbsmigrate, kbprb, kbtshoot, kberrmsg

↑ Back to the top

Article Info
Article ID : 916278
Revision : 2
Created on : 1/4/2008
Published on : 1/4/2008
Exists online : False
Views : 375