Error message when you try to open a full-text catalog after you restore a database in SQL Server 2005: "Property IsAccentSensitive is not available for FullTextCatalog '[CatalogName]'"

Symptoms

Consider the following scenario. In Microsoft SQL Server 2005, you restore a database from a backup. Then, you try to open a full-text catalog by using SQL Server Management Studio. In this scenario, you may receive the following error message:
Property IsAccentSensitive is not available for FullTextCatalog '[CatalogName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Additionally, when you try to query the information about the AccentSensitivity property of the full-text catalog by using the FULLTEXTCATALOGPROPERTY Transact-SQL function, you receive a null value. For example, you use a Transact-SQL statement that resembels the following statement to query the information about the
AccentSensitivity property.
select FULLTEXTCATALOGPROPERTY('<CatalogName>','AccentSensitivity')
When you use this statement, you receive a null value. However, when you query the information about the AccentSensitivity property directly from the full-text catalog view, SQL Server returns the correct information. For example, you can use the following Transact-SQL statement to query the information.
select * from sys.fulltext_catalogs
This problem may occur when the following conditions are true:
  • You back up a database and then delete the database.
  • The database has a full-text catalog.
  • Before you restore the database, you create one or more databases. Therefore, the previous database ID is used by the newly created databases.

Cause

This problem occurs because SQL Server stores a wrong full-text catalog name for the existing full-text catalog when you restore the database.

Workaround

To work around this problem, safely detach the restored database. Then, reattach the restored database. To do this, follow these steps:
  1. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.
  2. Run the following statement to detach the database that contains the full-text catalog:
    sp_detach_db 'dbname'
    GO
    Note The placeholder dbname represents the name of the database.
  3. Reattach the database to reset the catalog ID. To do this, take one of the following actions, depending on where the full-text catalogs are located:
    • If the full-text catalogs are located in the default location, attach the database by specifying the path of the database file. For example, you can attach the database by running one of the following statements:
      • sp_attach_db dbname, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbname_data.mdf'
        GO
      • CREATE DATABASE dbname ON 
        (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbname.mdf')
        FOR ATTACH
        GO
    • If the full-text catalogs are located in different locations, attach the database by specifying each catalog name together with the location of the catalog. For example, you can attach the database by running the following statement:
      CREATE DATABASE dbname ON 
      (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbname_data.mdf'),
      (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbname_log.ldf'),
      --optional folder name of FTS catalog 1
      (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\Catalog_1'),
      --optional folder name of FTS catalog 2
      (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\Catalog_2')
      FOR ATTACH;
      GO

Status

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

ID do Artigo: 910067 - Última Revisão: 16 de jun de 2008 - Revisão: 1

Comentários