how to delete non-existent full text catalog

  • Angus Lilburn-274633

    SSC Enthusiast

    Points: 123

    Hi,

    Having successfully restored a database to another server with all user/logins resolved, there remains an issue with the Full Text catalog. It was previously on an i: drive, but the new server has no i: drive.

    If I try to delete it, I get an error 20565 [SQL-DMO] Database is not full-text enabled yet.

    Is there a way of forcing the deletion of a non-existent full text catalog so I can create a new one, or of changing the location of the FTC ?

    Many Thanks,

    Angus

  • John Kane

    SSCrazy

    Points: 2810

    This is a bug related to moving / restoring FT-enabled databases to another

    server where the drive letter for the FT Catalogs is different than the

    orgianal server. You can work around this this "Catch-22" situation with the

    following code (Note: Sysadmin level permission is required):

    -- Enable System Table UPDATEs

    sp_configure allow,1

    go

    reconfigure with override

    go

    use <your_user_database_name>

    go

    -- Record FT Catalog info. (Note: path = NULL)

    select * from sysfulltextcatalogs

    go

    UPDATE sysfulltextcatalogs set path = '<new_drive_letter:\<new_directory>'

        WHERE ftcatid = <ftcatid_of_affected_catalog>

    go

    -- Record new FT Catalog info.

    select * from sysfulltextcatalogs

    go

    use master

    go

    -- Disenable System Table UPDATEs

    sp_configure allow,0

    go

    reconfigure with override

    go

    Once you execute the above SQL code, you should be able to delete the "non-existing" FT Catalog. If not, let me know the exact version (@@version) that you are using and I can email you a SQL script that will clear up this issue for you.

    Thanks,

    John

    SQL Full Text Search Blog

    http://spaces.msn.com/members/jtkane/


    John T. Kane

  • Eric-230578

    SSC Enthusiast

    Points: 148

    I am having a similair issue and after the update to sysfulltextcatalog still get the error message. ANy suggestions?

  • srinivasan127

    Valued Member

    Points: 73

    i AM ALSO FACING THE SAME PROBLEM.

    ERROR:21776 [SQL-DMO] THE NAME 't1' was not found in the full text catalogs collection. If the name is qualified name, use[] to separate various parts of the name and try again'.

    Kindly advise.

    Thanks and regards,

    S.Srinivasan

  • Pavlo Ozeruga-414414

    SSC Journeyman

    Points: 77

    It works! To get ftcatid:

    "select * from sysfulltextcatalogs"

    execute this on DB that has catalog.

  • aloha1973

    SSC Enthusiast

    Points: 155

    it does not work in SQL 2005 as i receive the following message:

    Msg 259, Level 16, State 1, Line 1

    Ad hoc updates to system catalogs are not allowed.

    It does not allow the changes to sysfulltextcatalogs.

    Help!!!

  • yulz

    SSC Journeyman

    Points: 95

    Get the filename from the original server and restore with move to a new drive

    When the database is restored on SQL Server 2012 from SQL server 2005, a new database file will be created for the full-text catalog.

    http://mssqlfun.com/2012/11/19/restoring-a-sql-server-2005-full-text-catalog-to-sql-server-2012/

  • johnwalker10

    SSCrazy Eights

    Points: 9074

    Try this syntax to drop full text catalog

    DROP FULLTEXT CATALOG catalog_name

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply