A couple of days ago while working with SQL Server, I found myself in a catch 22 situation .We recently moved our database server and copied all objects including full text catalogs to a new location. While enabling the full text search on the new database, I realized that full text catalogs are still pointing to old location and needs changing. Frankly speaking, I didn't know how to do that and decided to drop the index and recreate as an easy way around it. However, in an effort to do so, I got an error saying "Full text is not enabled on the database. Run sp_fulltext_database 'enable'". When I ran that sp, I got another error saying "F:\MSSQL\FTData\SH_FT_CA..." doesn't exist". It was interesting enough to know that I couldn't drop the catalogue because of being disabled, but couldn't enable it either as it didn't exist at all. Wow! What a deadlock.
Well, with a little effort, I figured out a manual work around which is nothing but running an update command on sysfulltextcatalogs table. However, before I do that, I had to enable the updates for the current server by executing sp_configure for 'allow_updates' option. Long story short, following set of queries helped me update the path and eventually made it possible to drop the catalogues.
SELECT * FROM sysfulltextcatalogs
SP_CONFIGURE 'allow_updates', 0 GO RECONFIGURE WITH OVERRIDE GO
UPDATE SYSFULLTEXTCATALOGS SET path = 'D:\Microsoft SQL Server\MSSQL\FTData' WHERE ftcatid = 5
0 comments:
Post a Comment